Implementation code for returning numeric values in dynamic SQL _mssql

Copy Code code as follows:

ALTER proc [dbo]. [Sp_common_paypal_addinfo]
(
@paypalsql varchar (max),--Paypalsql statement that does not contain a user table
@paypalusersql varchar (max),--The SQL statement for the PayPal user table
@ebaysql varchar (max),--Ebaysql statement that does not contain a user table
@ebayusersql varchar (max),--an ebay user table SQL statement
@paypaluserwhere varchar (max),--PayPal user table Query ID statement
@ebayuserwhere varchar (max),--ebay user table Query ID statement
@websql varchar (max),--the Web to drop a user table SQL statement
@webusersql varchar (max),--The SQL statement for the Web user table
@webwhere varchar (max),--The SQL statement after the Web user table where
@ebaystockflag varchar (a),--ebay order number generation rule
@webstockflag varchar (a)--web order number generation rules
)
As
Set XACT_ABORT on
BEGIN TRANSACTION Mytrans
Begin try
Declare @uid int--find a user ID based on a statement
DECLARE @execsql varchar (max)
Declare @ebayuid int--find a user ID based on a statement
declare @execebaysql nvarchar (max)--sp_executesql field type must be nvarchar
declare @sql nvarchar (max)--sp_executesql field type 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 not, the obtained value may be null with Len () to get no length
--There is a PayPal user ID
if (@uid >0)
Begin
Set @execsql = @paypalsql--Presence of user information
Set @execsql = REPLACE (@execsql, ' @uid ', ' +convert (varchar, @uid) + ')
End
Else
Begin
Set @execsql = @paypalusersql + @paypalsql-no user information exists
End
if (LEN (@websql) >0)--Executes the web statement
Begin
exec sp_common_websiteorder_addinfo @websql, @webusersql, @webwhere, @webstockflag
End
if (LEN (@ebaysql) >0)--Execute 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--exists ebayuid
Set @execebaysql = replace (@execebaysql, ' @ebayuid ', ' +convert (varchar, @ebayuid) + ')--must be replaced otherwise it will report an error saying you must declare a scalar variable
End
Else
Begin
Set @execebaysql = @ebayusersql + @ebaysql--there is no ebayuid
End
Set @execebaysql = REPLACE (@execebaysql, ' @00 ', dbo. Getordernum (@ebaystockflag))--Call function Replacement 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