August 4, 2011 at 2:24 am
is it possible to run an SQL statement that is held within a column of a table?
example table:
declare @table table (ID int,sqlCode varchar(max))
insert into @table(ID,sqlCode)
select 1,'select * from sys.columns'
insert into @table(ID,sqlCode)
select 2,'select * from sys.columns where precision = 10'
select * from @table
my ultimate goal is to have a Stored Procedure which creates a table variable, creates and inserts the SQLCode - which would be Stored Procedure calls - into the SqlCode column within that table variable, and then be able to run that whole table as one statement.
so to use the above example code, i would do something like
exec (select sqlCode from @table)
and that would run the two SELECT statements instantly.
August 4, 2011 at 2:46 am
The answer to your first question: Yes, it is possible to run an SQL statement that is held within a column of a table?
The answer to your last question: Yes with reservations, you can run the two SELECT statements in a single call using dynamic SQL. Reservation is: two select statements can run one after another, not instantly. Also, I cannot see any benefits in doing this. You can do it one by one in the loop - same effect, less problem, clear code.
But if you are desperate to do the way you like here is sample:
In order to get multiple statements to run in one go you will need to collect them into single variable:
DECLARE @sql nvarchar(max)
SET @sql = ''
SELECT @sql = @sql + ' ' + sqlCode + ';
'
FROM @table
EXEC (@sql)
I would also recommend to use sp_executesql instead of EXEC statements.
August 4, 2011 at 3:17 am
i see, thanks.
My thought process was, if the Stored Procedure code was created within the column of a Table, and then i was able to run all those Stored Procedures with one call by doing some sort of Select statement on it, would that would be faster than a Cursor?
by the sounds of your reply, it seems not?
August 4, 2011 at 4:36 am
In a sample you have posted, you have separate independent select statements, so it will be no much performance difference in running:
SET @sql = 'Select * from table1;' + 'Select * from table2;'
EXEC (@sql)
or
SET @sql = 'Select * from table1;'
EXEC (@sql)
SET @sql = 'Select * from table2;'
EXEC (@sql)
Using cursor in this case is totally acceptable.
But I don't know what all of your requirements are, so it may be you can change design and you will not need to run multiple statements at all and therefore you will not need a cursor.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply