SQL中的函数

函数如下：

ALTER FUNCTION  [dbo].[F_IMeetGetIconBySupplierID](    

@supplierid INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    
    IF exists(SELECT imeetsupplierid FROM T_IMeetSupplierIcon WHERE imeetsupplierid = @supplierid) // 函数中这条语句可循环
    BEGIN
        DECLARE @temp VARCHAR(2000)
        DECLARE @str VARCHAR(MAX)
        SET @temp =''
        SELECT
        @temp = @temp + T_IMeetSupplierIcon.IconName +'^'+T_IMeetSupplierIcon.linkurl +'^'+T_IMeetSupplierIcon.iconpath +'|'
        FROM T_IMeetSupplierIcon
        WHERE imeetsupplierid = @supplierid
        SELECT @temp  = SUBSTRING(@temp,2, LEN(@temp)-2)
    END
    ELSE
    BEGIN
        SET @temp = ''
    END
    RETURN @temp

END

 

存储过程调用如下：

  CREATE PROCEDURE dbo.GetSupplierIconBySupplierId
    @supplierId int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT dbo.F_IMeetGetIconBySupplierID(@supplierId) as Icons
END
GO

 

再看另外一个存储过程的调用如下：

 SELECT
                row_number() OVER (ORDER BY bb.SNETAdvertisementRelateParentID DESC, sp.suppliername ASC )AS RowID,
                    s.*,
                    ISNULL('+CAST(ISNULL(@CountRow,0) AS NVARCHAR(10))+',0)AS CountRow,
                    sp.SupplierName,
                    sp.City,
                    sp.Metroregion,
                    sp.Country,
                    sp.Website,
                    Cast(sp.SupplierType AS nvarchar(30))AS SupplierType,
                    ISNULL(sr.MemberRating,0) AS MemberRating,
                    sp.Email,
                    bb.SNETAdvertisementRelateParentID,
                    bb.SNETAdvertisementLogFilePath,
                    dbo.F_IMeetGetIconBySupplierID(sp.imeetsupplierid) as Icons,
                    ISNULL(sr.SupplierRating,-1) AS SupplierRating
                FROM T_IMeetSupplier s                   
                    INNER JOIN T_IMeetSupplierProfile sp ON sp.IMeetSupplierID=s.IMeetSupplierID
                    
                    AND sp.SupplierName LIKE '''+@LikeName+'''
                    AND sp.Email <> ''''
                    AND NOT sp.Email IS NULL