Implementation Code for returning values in dynamic SQL statements

Copy codeThe Code is as follows:
ALTER proc [dbo]. [sp_common_paypal_AddInfo]
(
@ Paypalsql varchar (max), -- does not contain the paypalsql statement of the User table
@ Paypalusersql varchar (max), -- SQL statement of the paypal User table
@ Ebaysql varchar (max), -- the ebaysql statement that does not contain the user table
@ Ebayusersql varchar (max), -- ebay User table SQL statement
@ Paypaluserwhere varchar (max), -- ID statement for querying the paypal User table
@ Ebayuserwhere varchar (max), -- ID statement for querying the ebay User table
@ Websql varchar (max), -- remove the SQL statement of the User table from the web
@ Webusersql varchar (max), -- SQL statement of the web user table
@ Webwhere varchar (max), -- SQL statement after where in the web user table
@ Ebaystockflag varchar (10), -- ebay Order Number Generation rule
@ Webstockflag varchar (10) -- web order number generation rule
)
As
Set xact_abort on
Begin transaction mytrans
Begin try
Declare @ uid int -- query the user ID based on the statement
Declare @ execsql varchar (max)
Declare @ ebayuid int -- query the user ID based on the statement
Declare @ execebaysql nvarchar (max) -- The Field Type of sp_executesql must be nvarchar.
Declare @ SQL nvarchar (max) -- The Field Type of sp_executesql must be 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) -- if this is not the case, the obtained value may be null and the length cannot be obtained using len ().
-- The paypal user ID exists.
If (@ uid> 0)
Begin
Set @ execsql = @ paypalsql -- User information exists
Set @ execsql = REPLACE (@ execsql, '@ uid', ''+ convert (varchar, @ uid) + '')
End
Else
Begin
Set @ execsql = @ paypalusersql + @ paypalsql -- User information does not exist
End
If (LEN (@ websql)> 0) -- execute web statements
Begin
Exec sp_common_WebSiteorder_AddInfo @ websql, @ webusersql, @ webwhere, @ webstockflag
End
If (LEN (@ ebaysql)> 0) -- execute the ebay statement
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 exists
Set @ execebaysql = REPLACE (@ execebaysql, '@ ebayuid', ''+ convert (varchar, @ ebayuid) +'') -- must be replaced or an error is reported, indicating that the scalar variable must be declared.
End
Else
Begin
Set @ execebaysql = @ ebayusersql + @ ebaysql -- ebayuid does not exist
End
Set @ execebaysql = REPLACE (@ execebaysql, '@ 00', dbo. GetOrderNum (@ ebaystockflag) -- call the function to REPLACE the order number
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