自定义表类型参数的存储过程的调试技巧

来源：互联网发布：三了个七淘宝买家秀编辑：程序博客网时间：2024/05/17 17:56

调试方法很简单，可能就是一时没有想到，记录到自己博客中，希望能与大家分享。

可以放一些测试数据给临时表，再将临时表的数据插入到表类型的参数中，传入存储过程进行调试。

-- 表类型如下：

USE [PX_PrjManage] GO   CREATE TYPE [dbo] . [SaveGHTData] AS TABLE (   [CT_ProNO] [int] NOT NULL,   [CT_WorkNO] [int] NOT NULL,   [CT_CPA] [decimal] ( 18 , 2 ) NULL,   [CT_Operator] [varchar] ( 50 ) NULL,   [CT_State] [int] NULL,   [CT_Version] [int] NULL ) GO

--存储过程如下：

ALTER PROCEDURE [dbo] . [SaveGHTData] ( @ProjType varchar ( 10 ),   @GHTData dbo . SaveGHTData Readonly ) AS BEGIN declare @tableName varchar ( 100 ), @SQL nvarchar ( 500 )   CREATE TABLE #r   (   CT_ProNO int , CT_WorkNO int , CT_CPA decimal ( 18 , 2 ),   CT_Operator   varchar ( 50 ), CT_State int , CT_Version int   )     if @ProjType = 'HT'   begin SET @tableName = 'dbo.CDMS_ContractInfo';   END   if @ProjType = 'GS' BEGIN   SET @tableName = 'dbo.CDMS_ConEstimate' ;   END   if @ProjType = 'TZ' BEGIN SET @tableName = 'dbo.CDMS_ConInvestment' ;    END    begin   transaction    BEGIN   insert into #r select * from @GHTData     set @SQL = ' delete from ' + @tableName + '  where CT_WorkNO in (select CT_WorkNO from #r) and CT_ProNO=(select top 1 CT_ProNO from #r)' ;     exec ( @SQL )    exec ( ' insert into ' + @tableName + '(CT_Guid,CT_ProNO,CT_WorkNO,CT_CPA,CT_UpdateDate,CT_Operator,CT_State,CT_Version) select NEWID(),CT_ProNO,CT_WorkNO,CT_CPA,convert(varchar(24),getdate(),120),CT_Operator,CT_State,CT_Version from #r ' )     drop table #r        END    if ( @@error <> 0 )     rollback transaction    else     commit transaction        if ( @@error > 0 )      return - 1     else      return 1 END

--调试代码如下：

CREATE TABLE #r ( CT_ProjectNO varchar ( 20 ), CT_WorkNO varchar ( 20 ), CT_CPA decimal ( 18 , 2 ), CT_UpdateDate varchar ( 50 ),   CT_Operator   varchar ( 50 ), CT_State int , CT_Version int ) declare    @testtbl dbo . SaveGHTData     insert into #r select '1' , '1' , 1800.00 , CT_UpdateDate = convert ( varchar ( 24 ), getdate (), 120 ), 'ABC' , 2 , 2 insert into @testtbl select * from   #r ;    drop table     #r          exec   dbo . SaveGHTData 'GS' , @testtbl