mssql自定义函数中使用游标

create FUNCTION [dbo].[F_getsortname] 
(
    @pid varchar(1000)
)
RETURNS  nvarchar(4000)
AS
BEGIN 
    declare @name nvarchar(100)  
    declare @t nvarchar(4000)      
    DECLARE @MyCursor CURSOR   
    set @pid=','+@pid+','    
    --局部游标 
    set @MyCursor=CURSOR FOR select sortname from dbo.sort where  charindex(','+rtrim(sortid)+',', @pid)>0 --替换如in(1,2,3)
    set @t=''
    open @MyCursor
    FETCH NEXT FROM @MyCursor INTO @name 
    while @@FETCH_STATUS = 0
    begin 
         if len(@t)=0
         begin
             set @t=@name 
         end
         else
         begin
            set @t=@t+','+@name
         end  
         FETCH NEXT FROM @MyCursor INTO @name
    end
    CLOSE @MyCursor 
    DEALLOCATE @MyCursor 
    return @t
END

 

转载于:https://www.cnblogs.com/lecone/archive/2012/05/21/2511629.html