Implementation Code for returning values in dynamic SQL statements

Source: Internet
Author: User
Recently, I am working on a paypal data capture program. As there is a ing relationship between all fields and paypal, all SQL statements must be spliced and uploaded to the stored procedure for execution.

Recently, I am working on a paypal data capture program. As there is a ing relationship between all fields and paypal, all SQL statements must be spliced and uploaded to the stored procedure for execution.

The 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.