CODEWITHSUNDEEP
sql-server
Steve Staple
Steve Staple

Reputation: 3289

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

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)

Upvotes: 0

Views: 472

Answers (1)

Elliveny
Elliveny

Reputation: 2113

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

Upvotes: 2

Related Questions