CREATE PROCEDURE [dbo].[TEST]

AS

BEGIN

    DECLARE

        @con    nvarchar(50)='N8-4F',    --構建SQL需要的條件

        @sql        nvarchar(1000),--構建後的SQL語句

        @cnt        int,            --獲取執行結果值

        @curTmp    nvarchar(50)       

    --查詢滿足條件的個數

    SET @sql = 'SELECT @cnt = COUNT(*) FROM [EMPCARDHISTORY] WHERE 1=1 AND [AREA]='''+@con+''''

    EXEC sp_executesql @sql,N'@cnt int out',@cnt out  --可以獲得@cnt的值

    --演示SQL中有傳入參數

    SET @sql = ' SELECT @cnt = COUNT(*) FROM [EMPCARDHISTORY] WHERE 1=1 AND [AREA]=@con'

    EXEC sp_executesql @sql,N'@cnt int out,@con nvarchar(50)',@cnt out,@con

    --演示遊標

    SET @sql = ' DECLARE cur_getData FOR SELECT EMPNO FROM [EMPCARDHISTORY] WHERE 1=1 AND [AREA]=@'''+@con+''''

    EXEC(@sql)

    OPEN cur_getData        --該處的遊標是我們定義在@sql中的

    FETCH  NEXT FROM cur_getData INTO @curTmp

    WHILE @@FETCH_STATUS = 0

        BEGIN

            --xxxx

            FETCH  NEXT FROM cur_getData INTO @curTmp

        END

END