September 30, 2010 at 9:16 am
Hi Guys I have a sql procedure in a table that I need to Execute... the statements of the proc is in different rows of a column... how can we execute the create proc statement from the table and create the proc?
Or there is a better way to do.. what I need is I need to create procs on the fly and the procs will be in a master database which should be created in other database that I create in one of the procedure... What I have done now is created a script that will insert the scripts of proc in a table.. . I need to execute it or find a better way to do this... Thanks guys for the time.
September 30, 2010 at 10:32 am
You can read the code from the table and store it in a variable
select @col = col1
from mytable
And then execute it
exec(@col)
It you need to change databases, I'd query from the database, using 3 part naming to get the table in master.
Note that I would recommend you not store this in master, but store it in some database you create for central access from other databases.
September 30, 2010 at 11:34 am
Each line of sql text of Proc is in different row... sorry by master I meant main database and not master db
September 30, 2010 at 1:20 pm
Try something like this
DECLARE @cmd nvarchar(max)
SELECT
@cmd = COALESCE(@cmd + N' ' + [sqlrow],[sqlrow])
FROM
table_with_sql_in_it
ORDER BY
lineorderfield
EXEC sp_executesql @cmd
September 30, 2010 at 9:51 pm
thanks a lot mtassin... I was doing this earlier little differently and was failing due to line comments (--comments)... but used it again using ur logic.. it works. This is what I needed and much easier than other solutions I was thinking about. Thanks again.
October 1, 2010 at 10:17 am
Thanks... if you have line comments you'll also probably want to replace the N' ' with
N' ' + nchar(10) + nchar(13)
That way each line gets put onto its own line for when you need to read the stored proc.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply