自訂函數+遊標，函數遊標

自訂函數+遊標，函數遊標


use yuyongTest
go

create function dbo.getTotByName(@_name nvarchar(50))
returns int
as
begin
   declare @tot int;
   set @tot=0;
   declare @name nvarchar(50);
   set @name='';
   declare stuName cursor
   for
   select Name from student;
   --開啟遊標
   Open stuName;

   fetch next from stuName Into @name; 
   While ( @@Fetch_Status=0)  
      begin
         if(@name=@_name)
            set @tot=@tot+1;
         fetch Next From stuName Into @name;
      end
   --關閉遊標  
   close stuName;
   --釋放遊標
   deallocate stuName;
   return @tot;
end
go

declare @t int;
select @t=dbo.getTotByName('qwe');
select @t;