How to execute SQL statements saved in a table with T-SQL

Question

Is it possible to execute a SQL statement Stored in a Table, with T-SQL?

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

The statements that are stored in the table are ad-hoc statements which could be SELECT TOP 100 * FROM ATable to 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

I want to execute the @Query Variable that I declared from T-SQL. Is this possible? (I am running a MSSQL 2005 enviroment)

Answer 1

I have a similar situation I just can't get to work. I'd like to create a table, where we have a customer number, and the business rules used to determine codes for a variety of items. The table psudo looks like this:

| CustomerNumber | RuleName       | Rule                             |
----------------------------------------------------------------------
| 12345          |ShippingCharged | iif(id.ItemID in (1,2,3,4,5,6) AND 
                                    cod.Code IN (5,6,7,8),1,0)
| 99999          |ShippingCharged | iif(id.ItemID in (1,2,3,7,9,10) AND 
                                    cod.Code NOT IN (5,7,8),1,0)

I want to run a SELECT in the form:

SELECT CustomerNumber, RuleName, Rule as Value
FROM CustomerRules cr
JOIN CustomerData cd 
     ON cd.CustomerNumber = cd.CustomerNumber
JOIN ItemsData id 
     ON cd.ItemID = id.ItemID
JOIN CodesData cod 
     ON cd.Code = cod.Code
WHERE cr.RuleName = 'ShippingCharged' 

To return The Customer Number, the name of the Rule used and the calculated value of the IIF statement. I am getting the text of the Rule calculation, rather than the calculated value.

I've tried various forms of DSQL and TSQL but can't seem to get the column to be treated as a function, just a text value from the table.

Any ideas?

Answer 2

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

Answer 3

We use a much simpler approach. Store the scripts (raw sql or stored procedure calls) in a table with a column containing an identifying code for said script. Use placeholders in your script for parameters. Any scripts that are used a lot can be "keyed" in your app or web config file. If scripts need to be executed in a specific order, put an ordinal column in the table. The actual "script" can then be pulled into a c# list or array, passed to a 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 said scripts without recompiling your main application.

Answer 4

The previous answer allows you to run one statement, and is valid. The question was on how to run SQL Statements stored in a table, which I took as more than one statement being executed. For this extra step, there is a while loop involved to iterate through each statement that need 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 Summary, I created a temp table and put some SQL in it, using a IDENTITY (RID) field to provide an iterator for the while loop. Then ran the while loop. In the example, you should return two views of your SQL Version. I built this on 2k8, and I hope it helps someone out of a jam one day..

Answer 5

You can use

EXECUTE sp_executesql @Query

to run your T-SQL

Here's a link to the MS docn for SQL Server 2005

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