I want to use the output from a query as Dynamic SQL

Question

My Dynamic SQL queries have been placed inside a table. I want to read these from the table (in SQL server), do a parameter substitution, then execute the dynamic query.

i.e. Column GetFieldServerSQL contains the following:

SELECT top 1 Tenancy.LeadName 
FROM Tenancy 
RIGHT OUTER JOIN Property ON Tenancy.KeyProperty = Property.KeyProperty 
WHERE (Tenancy.EndDate IS NULL) and Property.KeyProperty = @PropertyID

This is what I have tried:

declare @sql nvarchar(1000)
declare @sql2 nvarchar(1000)
declare @res nvarchar(1000)
declare @result nvarchar(1000)

set @sql = 'SELECT [GetFieldServerSQL]
FROM [SVSCentral].[dbo].[SVSSurvey_ExternalField] where ID=5'

exec @res = sys.sp_executesql @sql
print @res

This returns my query in the Results window, but I want it as a variable. @res only contains a 0 (for success)

Once I have it as a variable, I want to do a substitution. Something like:

set @sql2 = REPLACE(@result,'@propertyID','1003443')

(supposing @result is where my results is stored)

And then execute it:

exec (@sql2)

Elliveny · Accepted Answer · 2015-02-09 10:57:02Z

2

Instead of doing this:

exec @res = sys.sp_executesql @sql

You need to insert the results into a table, then select from that table, like this:

DECLARE @resTable TABLE (res nvarchar(1000))
INSERT INTO @resTable (res)
exec (@sql)

SELECT @res=res from @resTable
print @res

answered Feb 9, 2015 at 10:57

Elliveny

2,1132 gold badges23 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

I want to use the output from a query as Dynamic SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related