动态SQL中返回数值的实现代码

复制代码 代码如下:

ALTER proc [dbo].[sp_common_paypal_AddInfo]

(

@paypalsql varchar(max),–不包含用户表的paypalsql语句

@paypalusersql varchar(max),–paypal用户表的sql语句

@ebaysql varchar(max),–不包含用户表的ebaysql语句

@ebayusersql varchar(max),–ebay的用户表sql语句

@paypaluserwhere varchar(max),–paypal用户表查询ID语句

@ebayuserwhere varchar(max),–ebay用户表查询ID语句

@websql varchar(max),–web除去用户表的sql语句

@webusersql varchar(max),–web用户表的sql语句

@webwhere varchar(max),–web用户表where之后的sql语句

@ebaystockflag varchar(10),–ebay订单号生成规则

@webstockflag varchar(10)–web订单号生成规则

)

as

set xact_abort on

begin transaction mytrans

begin try

declare @uid int–根据语句查找用户ID

declare @execsql varchar(max)

declare @ebayuid int–根据语句查找用户ID

declare @execebaysql nvarchar(max)–用sp_executesql 字段类型必须是nvarchar

declare @sql nvarchar(max)–用sp_executesql 字段类型必须是nvarchar

set @sql=’select @a=ID from tb_TransactionCustomer where ‘+ convert(varchar(8000),@paypaluserwhere)

exec sp_executesql @sql,N’@a int output’,@uid output

set @uid =ISNULL(@uid,0)–如果不这样判断 获取的值可能为null用len()获取不到长度

–存在paypal用户id

if(@uid>0)

begin

set @execsql=@paypalsql– 存在用户信息

set @execsql= REPLACE(@execsql,’@uid’,”+convert(varchar,@uid)+”)

end

else

begin

set @execsql=@paypalusersql+@paypalsql –不存在用户信息

end

if(LEN(@websql)>0)–执行web语句

begin

exec sp_common_WebSiteorder_AddInfo @websql, @webusersql, @webwhere ,@webstockflag

end

if(LEN(@ebaysql)>0)–执行ebay语句

begin

–exec sp_common_Ebay_AddInfo @ebaysql, @ebayusersql, @ebayuserwhere ,@ebaystockflag

SELECT * FROM tb_EbayOrder WITH (TABLOCKX)

SELECT * FROM tb_EbayOrderList WITH (TABLOCKX)

SELECT * FROM tb_EbayOrderUserInfo WITH (TABLOCKX)

set @sql=’select @b=ID from tb_EbayOrderUserInfo where ‘+ convert(varchar(8000),@ebayuserwhere)

exec sp_executesql @sql,N’@b int output’,@ebayuid output

set @ebayuid =ISNULL(@ebayuid,0)

if(@ebayuid>0)

begin

set @execebaysql=@ebaysql–存在ebayuid

set @execebaysql= REPLACE(@execebaysql,’@ebayuid’,”+convert(varchar,@ebayuid)+”)–必须替换 否则会报错误说必须声明标量变量

end

else

begin

set @execebaysql=@ebayusersql+@ebaysql –不存在ebayuid

end

set @execebaysql= REPLACE(@execebaysql,’@00′,dbo.GetOrderNum(@ebaystockflag))–调用函数替换订单编号

exec (@execebaysql)

end

exec(@execsql)

end try

begin catch

if(@@TRANCOUNT>0)

rollback transaction mytrans

end catch

if(@@TRANCOUNT>0)

begin

commit transaction mytrans

end

else begin

rollback transaction mytrans

end