SQL Server Performance Forum – Threads Archive
INSERT Statement within Dymanic SQLHi All I have a dynamic SQL built using a cursor (@ variables)
For every row fetch from the cursor, i do further manipulations and select data from other tables, set values for more dynamic variables like @email,@message etc…and finally use xp_sendmail to send a detailed mail built using the cursor. I also want to create a table and insert all the above mentioned details, before sending out the mail, so that i can track all values in future.
But INSERT is not working at all.
Is an INSERT Allowed within a dynamic SQL ?? Please refer the SQL below. Thanks a tonne. Ex :
INSERT INTO PS_IM_ACHMAILTRK1
from PS_IM_ACH_PYMT_TMP where
PROCESS_INSTANCE = @im_ProcessId
and BANK_SETID = @im_BankSetId
and BANK_CD = @im_BankCd
and BANK_ACCT_KEY = @im_BankAcctKey
and PYMNT_ID = @im_PymntId
order by INVOICE_DT Thanks
You need to make your statement into a string (for example @SQLStatement) and execute it using: EXEC @SQLStatement Be sure to validate the values in your variables to ensure that nothing unwanted is executed!