How to execute SQL statements stored in a table using T-SQL

Question

Can I execute an SQL statement stored in a table using T-SQL?

DECLARE @Query text SET @Query = (Select Query FROM SCM.dbo.CustomQuery) 

The statements stored in the table are ad-hoc operations, which can be SELECT TOP 100 * FROM ATable for more complex statements:

 Select J.JobName As Job, JD.JobDetailJobStart AS StartDate, JD.JobDetailJobEnd AS EndDate, ( SELECT (DATEDIFF(dd, JD.JobDetailJobStart, JD.JobDetailJobEnd) + 1) -(DATEDIFF(wk, JD.JobDetailJobStart, JD.JobDetailJobEnd) * 2) -(CASE WHEN DATENAME(dw, JD.JobDetailJobStart) = 'Sunday' THEN -1 ELSE 0 END) -(CASE WHEN DATENAME(dw, JD.JobDetailJobEnd) = 'Saturday' THEN -1 ELSE 0 END) ) AS NumberOfWorkingDays, JD.JobDetailDailyTarget AS DailyTarget, JD.JobDetailWeeklyTarget AS WeeklyTarget, JD.JobDetailRequiredQTY AS RequiredQuantity, ( Select SUM(sJL.JobLabourQuantityEmployees) From JobLabour sJL ) AS NumberOfEmployees, ( Select SUM((sEM.EmployeeDesignationDefaultRate * sJL.JobLabourQuantityEmployees)*8)*(SELECT (DATEDIFF(dd, JD.JobDetailJobStart, JD.JobDetailJobEnd) + 1) -(DATEDIFF(wk, JD.JobDetailJobStart, JD.JobDetailJobEnd) * 2) -(CASE WHEN DATENAME(dw, JD.JobDetailJobStart) = 'Sunday' THEN -1 ELSE 0 END) -(CASE WHEN DATENAME(dw, JD.JobDetailJobEnd) = 'Saturday' THEN -1 ELSE 0 END)) from EmployeeDesignation sEM Inner join JobLabour sJL on sJL.EmployeeDesignationID = sEM.EmployeeDesignationID ) AS FullEmployeeRate from Job J Inner Join JobDetail JD on JD.JobID = J.JobID Inner Join JobLabour JL on JL.JobID = J.JobID WHERE J.JobActive = 0 JD.JobDetailJobEnd) + Select J.JobName As Job, JD.JobDetailJobStart AS StartDate, JD.JobDetailJobEnd AS EndDate, ( SELECT (DATEDIFF(dd, JD.JobDetailJobStart, JD.JobDetailJobEnd) + 1) -(DATEDIFF(wk, JD.JobDetailJobStart, JD.JobDetailJobEnd) * 2) -(CASE WHEN DATENAME(dw, JD.JobDetailJobStart) = 'Sunday' THEN -1 ELSE 0 END) -(CASE WHEN DATENAME(dw, JD.JobDetailJobEnd) = 'Saturday' THEN -1 ELSE 0 END) ) AS NumberOfWorkingDays, JD.JobDetailDailyTarget AS DailyTarget, JD.JobDetailWeeklyTarget AS WeeklyTarget, JD.JobDetailRequiredQTY AS RequiredQuantity, ( Select SUM(sJL.JobLabourQuantityEmployees) From JobLabour sJL ) AS NumberOfEmployees, ( Select SUM((sEM.EmployeeDesignationDefaultRate * sJL.JobLabourQuantityEmployees)*8)*(SELECT (DATEDIFF(dd, JD.JobDetailJobStart, JD.JobDetailJobEnd) + 1) -(DATEDIFF(wk, JD.JobDetailJobStart, JD.JobDetailJobEnd) * 2) -(CASE WHEN DATENAME(dw, JD.JobDetailJobStart) = 'Sunday' THEN -1 ELSE 0 END) -(CASE WHEN DATENAME(dw, JD.JobDetailJobEnd) = 'Saturday' THEN -1 ELSE 0 END)) from EmployeeDesignation sEM Inner join JobLabour sJL on sJL.EmployeeDesignationID = sEM.EmployeeDesignationID ) AS FullEmployeeRate from Job J Inner Join JobDetail JD on JD.JobID = J.JobID Inner Join JobLabour JL on JL.JobID = J.JobID WHERE J.JobActive = 0 , JD.JobDetailJobEnd) * Select J.JobName As Job, JD.JobDetailJobStart AS StartDate, JD.JobDetailJobEnd AS EndDate, ( SELECT (DATEDIFF(dd, JD.JobDetailJobStart, JD.JobDetailJobEnd) + 1) -(DATEDIFF(wk, JD.JobDetailJobStart, JD.JobDetailJobEnd) * 2) -(CASE WHEN DATENAME(dw, JD.JobDetailJobStart) = 'Sunday' THEN -1 ELSE 0 END) -(CASE WHEN DATENAME(dw, JD.JobDetailJobEnd) = 'Saturday' THEN -1 ELSE 0 END) ) AS NumberOfWorkingDays, JD.JobDetailDailyTarget AS DailyTarget, JD.JobDetailWeeklyTarget AS WeeklyTarget, JD.JobDetailRequiredQTY AS RequiredQuantity, ( Select SUM(sJL.JobLabourQuantityEmployees) From JobLabour sJL ) AS NumberOfEmployees, ( Select SUM((sEM.EmployeeDesignationDefaultRate * sJL.JobLabourQuantityEmployees)*8)*(SELECT (DATEDIFF(dd, JD.JobDetailJobStart, JD.JobDetailJobEnd) + 1) -(DATEDIFF(wk, JD.JobDetailJobStart, JD.JobDetailJobEnd) * 2) -(CASE WHEN DATENAME(dw, JD.JobDetailJobStart) = 'Sunday' THEN -1 ELSE 0 END) -(CASE WHEN DATENAME(dw, JD.JobDetailJobEnd) = 'Saturday' THEN -1 ELSE 0 END)) from EmployeeDesignation sEM Inner join JobLabour sJL on sJL.EmployeeDesignationID = sEM.EmployeeDesignationID ) AS FullEmployeeRate from Job J Inner Join JobDetail JD on JD.JobID = J.JobID Inner Join JobLabour JL on JL.JobID = J.JobID WHERE J.JobActive = 0 , JD.JobDetailJobStart) = 'Sunday' Select J.JobName As Job, JD.JobDetailJobStart AS StartDate, JD.JobDetailJobEnd AS EndDate, ( SELECT (DATEDIFF(dd, JD.JobDetailJobStart, JD.JobDetailJobEnd) + 1) -(DATEDIFF(wk, JD.JobDetailJobStart, JD.JobDetailJobEnd) * 2) -(CASE WHEN DATENAME(dw, JD.JobDetailJobStart) = 'Sunday' THEN -1 ELSE 0 END) -(CASE WHEN DATENAME(dw, JD.JobDetailJobEnd) = 'Saturday' THEN -1 ELSE 0 END) ) AS NumberOfWorkingDays, JD.JobDetailDailyTarget AS DailyTarget, JD.JobDetailWeeklyTarget AS WeeklyTarget, JD.JobDetailRequiredQTY AS RequiredQuantity, ( Select SUM(sJL.JobLabourQuantityEmployees) From JobLabour sJL ) AS NumberOfEmployees, ( Select SUM((sEM.EmployeeDesignationDefaultRate * sJL.JobLabourQuantityEmployees)*8)*(SELECT (DATEDIFF(dd, JD.JobDetailJobStart, JD.JobDetailJobEnd) + 1) -(DATEDIFF(wk, JD.JobDetailJobStart, JD.JobDetailJobEnd) * 2) -(CASE WHEN DATENAME(dw, JD.JobDetailJobStart) = 'Sunday' THEN -1 ELSE 0 END) -(CASE WHEN DATENAME(dw, JD.JobDetailJobEnd) = 'Saturday' THEN -1 ELSE 0 END)) from EmployeeDesignation sEM Inner join JobLabour sJL on sJL.EmployeeDesignationID = sEM.EmployeeDesignationID ) AS FullEmployeeRate from Job J Inner Join JobDetail JD on JD.JobID = J.JobID Inner Join JobLabour JL on JL.JobID = J.JobID WHERE J.JobActive = 0 

I want to execute the @Query variable declared with T-SQL. Is it possible? (I am running MSSQL 2005)

Answer 1

you can use

 EXECUTE sp_executesql @Query 

to run T-SQL

Here is a link to MS docn for SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms188001%28v=sql.90%29.aspx

Answer 2

The previous answer allows you to run one statement and is valid. The question was how to run SQL expressions stored in a table, which I executed as several executable statements. This extra step requires a while loop to iterate through each statement that needs to be run.

 -- Author: Chad Slagle DECLARE @Table table (RID BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED, SQLText NVARCHAR(MAX) ) DECLARE @StatementMax INT ,@statementMin INT ,@isTest TINYINT = 1 ,@SQLStatement NVARCHAR(MAX) -- Insert SQL Into Temp Table INSERT INTO @table (SQLText) VALUES ('SELECT @@Version'); INSERT INTO @table (SQLText) VALUES ('SELECT SERVERPROPERTY(''ProductVersion'')') -- Get your Iterator Values SELECT @statementMAX = MAX(RID), @statementMIN = MIN(RID) FROM @table IF @isTest = 1 BEGIN SELECT *, @statementMax AS MaxVal, @StatementMin AS MinVal FROM @Table END -- Start the Loop WHILE @StatementMax >= @statementMin BEGIN SELECT @SQLStatement = SQLText FROM @table WHERE RID = @statementMin -- Get the SQL from the table IF @isTest = 1 BEGIN SELECT 'I am executing: ' + @SQLStatement AS theSqlBeingRun, GETDATE(), @statementMin, @StatementMax END ELSE BEGIN EXECUTE sp_ExecuteSQL @SQLStatement -- Execute the SQL END DELETE FROM @table WHERE RID = @statementMin -- Delete the statement just run from the table SELECT @statementMIN = MIN(RID) FROM @Table -- Update to the next RID IF @isTest = 1 BEGIN SELECT * FROM @table END END 

In the summary, I created a temporary table and put some SQL in it using the IDENTITY (RID) field to provide an iterator for the while loop. Then launched a while loop. In this example, you should return two views of your version of SQL. I built it on 2k8, and I hope that one day it will help someone from jam.

Answer 3

We use a much simpler approach. Store the scripts (raw sql calls or stored procedures) in a table with a column containing the identification code for the specified script. Use placeholders in script parameters. Any scripts that are used a lot can be locked in your application or web configuration file. If the scripts need to be executed in a specific order, place the ordinal column in the table. The actual "script" can then be pulled into a list or C # array passed to the database class library and executed accordingly. This gives you dynamic control over your SQL and allows you to make changes on the database side for the specified scripts without recompiling the main application.

Answer 4

 TRUNCATE TABLE AllTableUnion DECLARE @Query2 Nvarchar(MAX) SET @Query2='SELECT * FROM #UnionString t1)A' INSERT INTO AllTableUnion EXEC(@Query2) DECLARE @Query4 Nvarchar(MAX)=(SELECT Query FROM AllTableUnion) EXECUTE sp_ExecuteSQL @Query4