SQL Server Performance Forum – Threads Archive

INSERT Statement within Dymanic SQL

Hi 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
SELECT @counter1,GETDATE(),@im_ProcessInstance,@im_Email,@im_Subject,@im_Message,convert(varchar(12),INVOICE_DT,101),
str(GROSS_AMT,13,2)
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
Anand

---

Heya,
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!

---

]]>