模仿sp_executesql，制作一个自己的executesql脚本

参照sp_executesql 的使用语法格式是：

sp_executesql [ @stmt = ] stmt

[ 

    {, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' } 

     {, [ @param1 = ] 'value1' [ ,...n ] }

]

参考其中的一些参数的，返回的方法，这里制作一个 MyExecuteSql的存储过程，功能与sp_executesql相似。 这里提供可以输入9个可变参数，当然可以根据自己的爱好增加更多的参数。 这里免不了bug,最大的难点就是数据类型转换，有些数据类型，会转换不一致，有时间再研究研究。呵呵

Use  Test; Go If   Object_id ( ' [MyExecuteSql] ' , ' P ' )  Is   Not   null      Drop   Proc   [ MyExecuteSql ] Go Create   Proc   [ dbo ] . [ MyExecuteSql ] (      @stmt   nvarchar ( Max ),      @params   nvarchar ( Max ) = null ,      @param1   nvarchar ( Max ) = null  output,      @param2   nvarchar ( Max ) = null  output,      @param3   nvarchar ( Max ) = null  output,      @param4   nvarchar ( Max ) = null  output,      @param5   nvarchar ( Max ) = null  output,      @param6   nvarchar ( Max ) = null  output,      @param7   nvarchar ( Max ) = null  output,      @param8   nvarchar ( Max ) = null  output,      @param9   nvarchar ( Max ) = null  output ) As      Set  Nocount  On Begin  Try      Declare            @xml  xml,           @tmp1   nvarchar ( 1024 ),          @tmp2   nvarchar ( 1024 ),          @Define   nvarchar ( Max ),          @Set   nvarchar ( Max ),          @Update   nvarchar ( Max )      If   Object_id ( ' tempdb..# ' )  Is   Not   null          Drop   Table  #      Create   Table  #     (    id  smallint   Identity ( 1 , 1 )  Not   null ,         param  nvarchar ( 1024 ),         datatype  nvarchar ( 1024 ),         flag  char ( 1 ),         value  nvarchar ( max ),         IsOutPut  bit     )      /*     筛分 @params  */      Set   @xml = Convert (xml, Stuff ( Replace ( @params + ' , ' , ' @ ' , ' </a><a>@ ' ), 1 , 4 , '' ) + ' </a> ' )      Insert   Into  # (datatype)          Select  t.m.value( ' . ' , ' nvarchar(1024) ' )          From   @xml .nodes( ' /a ' ) t(m)           Update  #           Set      @tmp1 =Left (datatype, CharIndex ( '   ' ,datatype) - 1 ),             param = @tmp1 ,             IsOutPut = Case   When   Charindex ( '  out ' ,datatype) > 0   Then   1   Else   0   End  ,              @tmp2 = Reverse ( Stuff ( Reverse ( Replace ( Replace ( Replace ( Replace ( Replace (datatype, @tmp1 , '' ), '  output ' , '' ), '  out ' , '' ), ' input ' , '' ), '   ' , '' )), 1 , 1 , '' )),             datatype = @tmp2 ,             flag = Case   When                        CharIndex ( ' tinyint ' , @tmp2 ) > 0   Or                      CharIndex ( ' smallint ' , @tmp2 ) > 0   Or                      CharIndex ( ' int ' , @tmp2 ) > 0   Or                      CharIndex ( ' real ' , @tmp2 ) > 0   Or                      CharIndex ( ' money ' , @tmp2 ) > 0   Or                      CharIndex ( ' float ' , @tmp2 ) > 0   Or                      CharIndex ( ' bit ' , @tmp2 ) > 0   Or                      CharIndex ( ' decimal ' , @tmp2 ) > 0   Or                      CharIndex ( ' numeric ' , @tmp2 ) > 0   Or                      CharIndex ( ' smallmoney ' , @tmp2 ) > 0   Or                      CharIndex ( ' bigint ' , @tmp2 ) > 0   Or                      CharIndex ( ' varbinary ' , @tmp2 ) > 0   Or                      CharIndex ( ' binary ' , @tmp2 ) > 0   Or                      CharIndex ( ' timestamp ' , @tmp2 ) > 0                  Then   ''   Else   ''''   End                           /* 构造执行语句 */      Select            @Define = Isnull ( @Define + ' , ' , ' Declare  ' ) + param + '   ' + datatype,          @Set = Isnull ( @Set + ' , ' , ' Select  ' ) + param + ' =Convert( ' + datatype + ' , ' +              Case  id                   When   1   Then   Isnull (flag + @param1 + flag, ' null ' )                  When   2   Then   Isnull (flag + @param2 + flag, ' null ' )                  When   3   Then   Isnull (flag + @param3 + flag, ' null ' )                  When   4   Then   Isnull (flag + @param4 + flag, ' null ' )                  When   5   Then   Isnull (flag + @param5 + flag, ' null ' )                  When   6   Then   Isnull (flag + @param6 + flag, ' null ' )                  When   7   Then   Isnull (flag + @param7 + flag, ' null ' )                  When   8   Then   Isnull (flag + @param8 + flag, ' null ' )                  When   9   Then   Isnull (flag + @param9 + flag, ' null ' )              End + ' ) ' ,          @Update = Isnull ( @Update + '  When  ' , ' Update # Set value=Case id When  ' ) + Rtrim (id) + '  Then Convert(nvarchar(max), ' + param + ' ) '      From  #      Set   @stmt = @Define + char ( 13 ) + char ( 10 ) + @Set + char ( 13 ) + char ( 10 ) + ' Set Nocount Off ' + char ( 13 ) + char ( 10 ) + @stmt + char ( 13 ) + char ( 10 ) + ' Set Nocount On  ' + char ( 13 ) + char ( 10 ) + @Update + '  End '      Exec ( @stmt )      /* 输出参数 */      Select   @param1 = value  From  #  Where  id = 1   And  IsOutPut = 1      Select   @param2 = value  From  #  Where  id = 2   And  IsOutPut = 1      Select   @param3 = value  From  #  Where  id = 3   And  IsOutPut = 1      Select   @param4 = value  From  #  Where  id = 4   And  IsOutPut = 1      Select   @param5 = value  From  #  Where  id = 5   And  IsOutPut = 1      Select   @param6 = value  From  #  Where  id = 6   And  IsOutPut = 1      Select   @param7 = value  From  #  Where  id = 7   And  IsOutPut = 1      Select   @param8 = value  From  #  Where  id = 8   And  IsOutPut = 1     Select   @param9 = value  From  #  Where  id =9 And  IsOutPut = 1 End  Try Begin  Catch      Declare   @ErrMsg   nvarchar ( 1024 )      Set   @ErrMsg = Error_message()      Raiserror   50001   @ErrMsg End  Catch          

测试：

Declare   @x  numeric( 12 , 3 ) Set   @x = 56.31 Print   ' 结果： ' Print   ' 开始 @x= ' + Rtrim ( @x ) + char ( 13 ) + char ( 10 ) + char ( 13 ) + char ( 10 ) + ' 调用[MyExecuteSql]： ' exec   [ MyExecuteSql ] ' Select @2=@2*5;Select @1,@2,@3 ' , ' @1 nvarchar(20   )  input  ,@2 numeric(12,  3) output , @3   int  ' , ' sdf ' , @x  output, 34 Print   ' 输出 @x= ' + Rtrim ( @x )

/* 结果： 开始 @x=56.310 调用[MyExecuteSql]：                                                              -------------------- -------------------------- sdf                  281.550                                 34 (1 行受影响) 输出 @x=281.550 */