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

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

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

-- 表类型如下：

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