October 10, 2011 at 6:45 am
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
October 10, 2011 at 7:12 am
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
October 10, 2011 at 7:25 am
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
October 10, 2011 at 8:00 am
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.
October 10, 2011 at 8:05 am
ooohh yeahhhh that works ! much appreciated. Martin
October 10, 2011 at 11:27 am
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.”
October 11, 2011 at 1:56 am
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