sqlserver 存储过程实例

最新推荐文章于 2024-08-09 14:18:49 发布

weixin_30433075

最新推荐文章于 2024-08-09 14:18:49 发布

阅读量178

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/furenjian/p/4415059.html

版权

ALTER PROC [dbo].[SP_mm_NS] (
       @ID       NVARCHAR(60),
       @ReturnCode   NVARCHAR(30) OUTPUT,
       @ErrorMessage   NVARCHAR(2000) OUTPUT
)
AS
BEGIN
   DECLARE
       @rtnCode       NVARCHAR(30),
       @rtnText       NVARCHAR(2000)

   DECLARE
       @P_BillID NVARCHAR(60),
       @P_LineItemNo INTEGER

   SELECT @rtnCode = '0' ,@rtnText = ''

BEGIN TRY
           SELECT * FROM dbo.temp_table

            DECLARE Table_crsr CURSOR FOR SELECT
               CVLevel
               FROM #tempTable
           OPEN Table_crsr
           FETCH Table_crsr INTO

                   @CVLevel
           WHILE @@FETCH_STATUS = 0
           BEGIN
           ---

               FETCH Table_crsr INTO
                   @CVLevel
           END
           CLOSE Table_crsr
           DEALLOCATE Table_crsr

   DECLARE @C NVARCHAR(60)
            DECLARE @sql NVARCHAR(500)
            DECLARE @i INT
            SET @i=1
              WHILE(@i<=@CVLevel)
               BEGIN
                       SET @C ='CVLevel_'+ CONVERT(NVARCHAR(60), @i)
                       SET @sql = 'ALTER TABLE @Table ADD ' + @C + ' NVARCHAR(60)'
                       PRINT '@sql ='+@sql
                       EXEC(@sql)
                       SET @i=@i+1
               END

           END TRY
   BEGIN CATCH
       SET @rtnText= ' ERROR_PROCEDURE='+ ERROR_PROCEDURE()
                       +', ERROR_LINE='+convert(varchar(10), ERROR_LINE())
                       +', ERROR_NUMBER='+ convert(varchar(10), ERROR_NUMBER())
                       +', ERROR_MESSAGE='+left(ERROR_MESSAGE(), 255)
       SET   @rtnCode='1'

   END CATCH
L_END:

   SELECT @ReturnCode = @rtnCode, @ErrorMessage = @rtnText
END