Running SQL Statements that are stored within a table SQL Server 2014

Question

Hoping that I can get some assistance with something, I currently have a table with 3 columns, ID int, Name varchar(20), ScriptText varchar(500). ID is just a auto incremental ID, Name is a Name give to the ScriptText, ScriptText is a piece of SQL code, it will return a number or a single piece of text. What I want to do is run through each record, executing the ScriptText, once the script text is executed and returns a value to be stored in a variable, I will later need to compare this against another table. I have tried various ways but I can execute all the code in the ScriptText column but am not able to store the ID or Name in a variable so I can use all three of them. I have come across a piece of code that looks like it should be doing what I need it to do but cannot make it work.

    DECLARE @Table table (RID BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
                        SQLText NVARCHAR(MAX) )

DECLARE  @StatementMax INT 
        ,@statementMin INT
        ,@isTest TINYINT = 0
        ,@SQLStatement NVARCHAR(MAX)
        ,@StoreOutputData varchar(1000) = '';
-- Insert SQL Into Temp Table
INSERT INTO @table (SQLText)
VALUES ('SELECT @@Version');
INSERT INTO @table (SQLText)
VALUES ('SELECT SERVERPROPERTY(''ProductVersion'')')


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 

    IF @isTest = 1 BEGIN SELECT 'I am executing: ' + @SQLStatement AS theSqlBeingRun, GETDATE(), @statementMin, @StatementMax END  
    ELSE 
    BEGIN 
       EXECUTE sp_ExecuteSQL N'SELECT @OutVariable = CONVERT(NVARCHAR(1000), SERVERPROPERTY(''ProductVersion''))', N'@OutVariable varchar(1000) OUTPUT', @OutVariable = @StoreOutputData OUTPUT;
       SELECT @StoreOutputData;
    END
        DELETE FROM @table WHERE RID = @statementMin    
        SELECT @statementMIN = MIN(RID)  FROM @Table       
    IF @isTest = 1 BEGIN  SELECT * FROM @table END

END

Above amendments as per DK5

At the end when it executes the statements e.g. select @@Version I would expect it to show in the output as

"Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)"

as per my SQL installation but instead it just shows

"I am executing: SELECT @@Version"

If anyone could assist with making this example work or have a better suggestion for my problem it would be much appreciated.

My final result as per discussion should look like

RID      SQLText
1        Microsoft SQL Server 2014 - 12.0.4100.1 (X64) 
2        12.0.4100.1

I have used the above entire statement as an example as to put down everything would not be possible, at the end of the day I have a table that currently has 19 sql statements in it as well as various columns that have data related to the sql statement, hence the reason I want to bring back further information after just running the select statement.

Answer 1

It appears that the issue is with your Bit flag variable @isTest.

If you look at this code snippet:

IF @isTest = 1 BEGIN 
    SELECT 'I am executing: ' + @SQLStatement AS theSqlBeingRun, GETDATE(), @statementMin, @StatementMax 
END ELSE BEGIN 
    EXECUTE sp_ExecuteSQL @SQLStatement                 
END

When the variable @isTest is set to 1 then the script stored in the variable @SQLStatement is not executed. The script is appended as it is to the text in the SELECT clause. If the variable @isTest was set to something other than 1, then the ELSE branch of the If Else check would have been executed, which actually executes the statement.

The behaviour you want to see can be achieved by shifting the @isTest variable to anything other than 1 for example DECLARE @isTest int = 0.

There are more advanced things that can be done, such as using CROSS APPLY, but the issue you are facing is probably just related to this flag variable.

EDIT 1: To fetch data from sp_ExecuteSQL you can use this:

DECLARE @StoreOutputData varchar(1000) = '';
EXECUTE sp_ExecuteSQL N'SELECT @OutVariable = CONVERT(NVARCHAR(1000), SERVERPROPERTY(''ProductVersion''))', N'@OutVariable varchar(1000) OUTPUT', @OutVariable = @StoreOutputData OUTPUT;
SELECT @StoreOutputData;