How to dynamically execute SQL stored as text in a table

  • I have a table containing SQL statements stored in text columns, I want to write a S.P. that finds the row, executes the SQL then returns the results back. (The application generates the SQL for reports to be run which I intend to use as a datasource for reporting services)

    I manage to get the SQL into a variable -

    create procedure GetReport @SpoolID as int

    declare @sql nvarchar(4000)

    select @sql = 'select extrasql from reportspool where spoolid='+CONVERT(VARCHAR,@SpoolID)

    exec (@sql)

    This returns the SQL ok, eg SELECT * FROM ABC

    However exec('getreport 55055') does not actually execute the SQL it just returns SELECT * FROM ABC again.

    exec sp_executesql N'getreport5 55055' does the same thing

    Any ideas on how to do this?

    It did google for a few hours and couldn't see anything so over to you guys/gals!

    Thanks

    Martin

  • After correcting the obvious errors in your code as posted to:

    create procedure GetReport 999

    @SpoolID int

    AS

    declare @sql nvarchar(4000)

    select @sql = 'select extrasql from reportspool where spoolid='+CONVERT(VARCHAR,@SpoolID)

    print @sql

    --exec (@sql)

    Executing the above as:

    GetReport 999

    results in the print statement returning:

    select extrasql from reportspool where spoolid=999

    Note that I could not execute the T-SQL generated since you did not supply a table definition or sample data.

    Have you tested your code using sp_executesql 999

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Please, no need to make fun of my typos we're all here to help each other right?

    The table definition is fairly easy to work out but to make it easier:

    Create Table ReportSpool ( SpoolID Int, ExtraSQL text)

    Create Table Test (username varchar(10))

    insert into Test (username) values ('Martin')

    insert into ReportSpool (SpoolID, ExtraSQL) VALUES (10,'SELECT * FROM Test')

    I want to be able to execute a SP, eg GetReport 10 and it return 'Martin'

    That's it in a nutshell

  • How does this work for you?

    alter procedure GetReport

    @SpoolID int

    AS

    declare @sql nvarchar(4000)

    select @sql=extrasql from reportspool where spoolid=@SpoolID

    exec (@sql)

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • ooohh yeahhhh that works ! much appreciated. Martin

  • Hi martin in case you have time can you let me know why you were passing a int (variable) and equating it to varchar inside it

    ______________________________________________________________________

    Ankit
    MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
    "Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
    You are already naked. There is no reason not to follow your heart.”

  • Grasshoper sure, nice quote btw, well the stored procedure syntax is very interesting to me

    In my original attempt I was building up a string of SQL to be executed and so had to convert the int before adding it to the string.

    That did actually work up to a point, and I'm still hazy on why, but no matter, the fixed version bypasses building up a string of SQL to execute and is just raw SQL.. much neater and it also makes more sense!

    Regards

    Martin

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply