CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Kip Real
Kip Real

Reputation: 3469

sp_executesql Dynamically Store Queries in column

Using sp_executesql i would like to execute the results of a column that stores pre written queries on a column as show below.

DECLARE @ID AS INT =2
DECLARE @StoredQueries Table
(
ID INT IDENTITY(1,1),
ExtractSQL NVARCHAR(MAX)
)

INSERT INTO @StoredQueries
VALUES(
 'SELECT [DepartmentID]
      ,[Name]
      ,[GroupName]
      ,[ModifiedDate]
  FROM [AdventureWorks2008R2].[HumanResources].[Department]'
       )
INSERT INTO @StoredQueries
VALUES(
 'SELECT[ShiftID]
      ,[Name]
      ,[StartTime]
      ,[EndTime]
      ,[ModifiedDate]
  FROM [AdventureWorks2008R2].[HumanResources].[Shift]
  '
         )

INSERT INTO @StoredQueries
VALUES(
 'SELECT TOP 1000 [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[SpatialLocation]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2008R2].[Person].[Address]'
       )
  SELECT * FROM @StoredQueries  
  WHERE ID =@ID

Results from Dave.Gugg answer

DECLARE @ID AS INT =2
DECLARE @StoredQueries Table
(
ID INT IDENTITY(1,1),
ExtractSQL NVARCHAR(MAX)
)

INSERT INTO @StoredQueries
VALUES(
 'SELECT [DepartmentID]
      ,[Name]
      ,[GroupName]
      ,[ModifiedDate]
  FROM [AdventureWorks2008R2].[HumanResources].[Department]'
       )
INSERT INTO @StoredQueries
VALUES(
 'SELECT[ShiftID]
      ,[Name]
      ,[StartTime]
      ,[EndTime]
      ,[ModifiedDate]
  FROM [AdventureWorks2008R2].[HumanResources].[Shift]
  '
         )

INSERT INTO @StoredQueries
VALUES(
 'SELECT TOP 1000 [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[SpatialLocation]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2008R2].[Person].[Address]'
       )

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = ExtractSQL
FROM @StoredQueries
WHERE ID = @ID

EXEC sp_executesql @sql

Upvotes: 1

Views: 91

Answers (1)

Dave.Gugg
Dave.Gugg

Reputation: 6771

You're close, just replace that very last select with this:

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = ExtractSQL
FROM @StoredQueries
WHERE ID = @ID

EXEC sp_executesql @sql

Upvotes: 3

Related Questions