What are the challenges you face when working across database platforms? Take the survey
Options

Feature Request - Assist Dynamic SQL

DavidSteinDavidStein Posts: 11 Bronze 1
edited October 3, 2012 12:53PM in SQL Prompt Previous Versions
I need to save T-SQL scripts inside a database field to be executed by a SSIS package. This is not an uncommon thing. So, if I need to insert the script into the table and the script has single quotes, it would be nice if SQLPrompt (or a different project) could handle the multiple single quote issues with that.

Basically this:
IF EXISTS (SELECT TOP 1 * FROM Lookup.Country WHERE CountryCode = '{{')
BEGIN
   EXEC UnitTest.RaiseValidationErrors 'UPDATEBEFORE'
END
ELSE
IF NOT EXISTS (SELECT TOP 1 * FROM Lookup.Country WHERE CountryCode = '}}' AND CountryName = 'UPD')
BEGIN
   EXEC UnitTest.RaiseValidationErrors 'UPDATEAFTER'
   END
RETURN

Becomes this. (I added the Select so one could run it and see the script).
Select
'IF EXISTS (SELECT TOP 1 * FROM Lookup.Country WHERE CountryCode = ''{{'')
BEGIN
   EXEC UnitTest.RaiseValidationErrors ''UPDATEBEFORE''
END
ELSE
IF NOT EXISTS (SELECT TOP 1 * FROM Lookup.Country WHERE CountryCode = ''}}'' AND CountryName = ''UPD'')
BEGIN
   EXEC UnitTest.RaiseValidationErrors ''UPDATEAFTER''
   END
RETURN'

Comments

Sign In or Register to comment.