How to execute SQL statements saved in a table wit

问题:

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)

回答1:

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

回答2:

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..

回答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.