How to dynamically execute SQL stored as text in a table

Question

Post reply

How to dynamically execute SQL stored as text in a table

martinchantler

Old Hand

Points: 318
More actions
October 10, 2011 at 6:45 am

#244349

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

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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]

martinchantler Old Hand Points: 318 More actions · Answer 2

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

toddasd SSCertifiable Points: 6298 More actions · Answer 3

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.

martinchantler Old Hand Points: 318 More actions · Answer 4

martinchantler

Old Hand

Points: 318

October 10, 2011 at 8:05 am

#1392423

ooohh yeahhhh that works ! much appreciated. Martin

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 5

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.”

martinchantler Old Hand Points: 318 More actions · Answer 6

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