How to Execute SQL Statements Saved in a Table with T-Sql

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

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

SQL - execute query from table cells

This should help you go through all scripts in that table. With what you are doing, only one script will be run so if the table has 100 Scripts, only one will be executed. Hope this helps

DECLARE @Queries TABLE (ID INT IDENTITY(1,1),SQLScript VARCHAR(MAX))
DECLARE @STR_QUERY VARCHAR(MAX);
DECLARE @StartLoop INT
DECLARE @EndLoop INT

INSERT INTO @Queries
SELECT Query 
FROM [AccHelper].[dbo].[Queries]

SELECT @EndLoop = MAX(ID), @StartLoop = MIN(ID)
FROM @Queries

WHILE @StartLoop < = @EndLoop
BEGIN
    SELECT @STR_QUERY = SQLScript 
    FROM @Queries
    WHERE ID = @StartLoop

    EXEC (@STR_QUERY)

    SET @StartLoop = @StartLoop + 1
END

Execute A Dynamic SQL statement Stored in a Column of a table

Had to look up what `lakh' was :)

As other comments mentioned this is not most optimal approach to DML, please consider refactoring. As it is you can combine your dynamic SQL in batches, e.g.:

DECLARE @sSQL nvarchar(max)
SET @sSQL = 'BEGIN TRAN; '

SELECT @sSQL = @sSQL + COLUMN_WITH_INSERT_STATEMENT  + '; '
FROM TABLE
WHERE [limit number of rows]

SET @sSQL = @sSQL + 'COMMIT TRAN '

EXEC(@sSQL)

This way you can combine controlled number of INSERT statements into a single transaction. You can control number of inserts by means of WHERE statement (e.g. WHERE ID BETWEEN 1 and 100 to execute 100 INSERTs at a time) You can loop thru this condition (yes loop, but it it will not be looping thru individual rows, but thru conditions instead e.g.1 - 100, 101 - 200, 201 - 300 etc).

execute SQL query stored in a table

This seems a very peculiar requirement, and one which will be difficult to solve in a robust fashion. STMT_OR_VALUE is the embodiment of the One Column Two Usages anti-pattern. Furthermore, resolving STMT_OR_VALUE requires flow control logic and the use of dynamic SQL. Consequently it cannot be a pure SQL solution: you need to use PL/SQL to assemble and execute the dynamic query.

Here is a proof of concept for a solution. I have opted for a function which you can call from SQL. It depends on one assumption: every query string you insert into TEST1.STMT_OR_VALUE has a projection of a single numeric column and every value string is a CSV of numeric data only. With this proviso it is simple to construct a function which either executes a dynamic query or tokenizes the string into a series of numbers; both of which are bulk collected into a nested table:

create or replace function get_ids (p_name in test1.name%type) 
  return sys.odcinumberlist
is
  l_rec test1%rowtype;
  return_value sys.odcinumberlist;
begin

  select * into l_rec
  from test1
  where name = p_name;

  if l_rec.type = 'SQL_QUERY' then 
    -- execute a query
    execute immediate l_rec.stmt_or_value
      bulk collect into return_value;
  else
    -- tokenize a string
    select xmltab.tkn
    bulk collect into return_value
    from ( select l_rec.stmt_or_value from dual) t
        , xmltable(  'for $text in ora:tokenize($in, ",") return $text'
                      passing stmt_or_value as "in"
                      columns tkn number path '.'
                   ) xmltab;
  end if;
  return return_value;
end;
/

Note there is more than one way of executing a dynamic SQL statement and a multiplicity of ways to tokenize a CSV into a series of numbers. My decisions are arbitrary: feel free to substitute your preferred methods here.

This function can be invoked with a table() call:

select * 
from data
where id in ( select * from table(get_ids('first'))) -- execute query
or    id in ( select * from table(get_ids('second'))) -- get string of values
/

The big benefit of this approach is it encapsulates the logic around the evaluation of STMT_OR_VALUE and hides use of Dynamic SQL. Consequently it is easy to employ it in any SQL statement whilst retaining readability, or to add further mechanisms for generating a set of IDs.

However, this solution is brittle. It will only work if the values in the test1 table obey the rules. That is, not only must they be convertible to a stream of single numbers but the SQL statements must be valid and executable by EXECUTE IMMEDIATE. For instance, the trailing semi-colon in the question's sample data is invalid and would cause EXECUTE IMMEDIATE to hurl. Dynamic SQL is hard not least because it converts compilation errors into runtime errors.

Execute dynamic select query stored in a table

It's very poor practice storing SQL code in a table. Have you looked at views, stored procedures or inline table-valued functions?

Anyway, to execute the stored SQL in a table you would do something like this....

DECLARE @Sql NVARCHAR(MAX);

SELECT TOP 1 @Sql = RuleSql
FROM TableName  --<-- table where sql is stored   
WHERE <Some Condition>

Exec sp_executesql @Sql

Just saw your edit. To execute all the queries at once you would use a cursor something like.....

DECLARE @Sql NVARCHAR(MAX);

DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR 
SELECT  RuleSql
FROM TableName  --<-- table where sql is stored   

OPEN Cur

  FETCH NEXT FROM Cur INTO @Sql 

WHILE (@@FETCH_STATUS = 0)
BEGIN
     Exec sp_executesql @Sql
     FETCH NEXT FROM Cur INTO @Sql 
END

CLOSE Cur
DEALLOCATE Cur;

Executing T-SQL query from table

The actual reason for this error is the difference between EXEC @Test (to execute a stored procedure or function) and EXEC (@Test) (to execute a dynamic statement). Note, that you may use sp_executesql to execute a dynamically generated statement:

CREATE PROCEDURE MergeTable @TableName VARCHAR(256)
AS
BEGIN
    DECLARE @Test VARCHAR(max)
    
    SELECT @Test = Query
    FROM dbo.QueryMergeDWH
    WHERE SourceTableName = @TableName

    EXEC (@Test)
    -- Or using sp_executesql
    -- DECLARE @err int
    -- EXEC @err = sp_executesql @Test
    -- IF @err <> 0 PRINT 'Error'
END
GO

EXEC MergeTable @TableName = 'SGPREINVOICE'

Execute saved queries in table SQL Server

Use cursor over your table and execute as dynamic t-sql adding insert into at the beginning of every value with SQL statement.

CREATE TABLE tSELECT 
(
    query VARCHAR(1000)
);
INSERT INTO tselect VALUES ('Select Max(acc),Min(acc)  from my_table1')
CREATE TABLE result (min INT, max int);

BEGIN
    DECLARE @stmt VARCHAR(1000);
    DECLARE db_cursor CURSOR FOR  SELECT * FROM tselect;
    OPEN db_cursor  FETCH NEXT FROM db_cursor INTO @stmt
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
           EXEC('insert into result ' + @stmt);

           FETCH NEXT FROM db_cursor INTO @stmt   
    END   

    CLOSE db_cursor   
    DEALLOCATE db_cursor    
END;

Execute SQL Statement stored in table column

SET v_SQL=
        'SET ?=(
                    SELECT 
                    DATA_SQL
                    FROM TB_SQL
                    WHERE TBNAME='''||v_TBNAME||'''
               )
        ';
        PREPARE SQL_QUERY FROM v_SQL;
        EXECUTE SQL_QUERY INTO v_INSERT_STATEMENT;
        PREPARE SQL_INSERT FROM v_INSERT_STATEMENT;
        EXECUTE SQL_INSERT;

---

Related Topics