动态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