醫務室系統報表中使用的一個使用遊標的自定義方法 sqlserver

USE [BFMDB]
GO
/****** Object:  UserDefinedFunction [dbo].[GetProcessSetByRegNo]    Script Date: 11/04/2013 11:09:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  pcjbird
-- Create date: 2007/09/12
-- Description: 獲取處方單的處置類型合集
-- test sql: select dbo.GetProcessSetByRegNo(10) as ProcessName
-- =============================================
ALTER FUNCTION [dbo].[GetProcessSetByRegNo]
(
  @RegNo int
)
RETURNS nvarchar(100)
AS
BEGIN
   Declare @tmpresult nvarchar(500)
set @tmpresult=''

--聲明游標
declare currentcursor cursor
for 
(
select b.ProcessTypeCName as ProcessName
from Med_Diag_ProcessType a
Left OUTER JOIN
Med_ProcessType b
on a.ProcessType=b.ProcessType
where RegisterNo=@RegNo
)
--打開游標
open currentcursor
--用while循環控制游標活動
WHILE @@FETCH_STATUS = 0     
BEGIN
   declare @tmpProcessName nvarchar(50)
 
          FETCH NEXT from currentcursor into @tmpProcessName
           if  (@@FETCH_STATUS <> 0)
               break
           if @tmpresult=''
             begin
               set @tmpresult=@tmpProcessName
             end
           else
             begin
               set @tmpresult=@tmpresult+';'+@tmpProcessName
             end
END
--關閉游標
CLOSE currentcursor
--刪除游標
DEALLOCATE currentcursor

return @tmpresult
END