October 11, 2007 at 1:42 pm
Apologies if this one has already been answered. I thought I saw it here once and/or did it myself, but I can't find it, so here goes.
I want to use sp_executesql to execute a stored procedure. This procedure will have one input parameter and one output parameter and recordset output. To increase the level of difficulty, I will look up the name of the stored procedure to execute at run-time and place that procedure name in a variable.
I've followed the examples in BOL (although notably they only execute SQL, not stored procedures) and had no luck.
Has anybody done this and can you provide an example of the code? Or point me to the article/forum entry where this was previously covered?
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
October 11, 2007 at 2:44 pm
If it makes you feel any better debugging sp_executesql can be a royal pain.
Here is a quicky sample
declare @tablename nvarchar(50)
declare @sql nvarchar(1000)
SET @tablename = 'sysobjects'
set @sql = 'dbcc checktable (@tablename)'
exec sp_executesql @sql, N'@tablename nvarchar(50)', @TableName
I realize I'm only passing in 1 parameter and not getting any back but like I said .. its a quicky. If you would like to post what you are doing I might be able to help more.
One thing that happens to me alot though is not passing it a single string variable for the command. For example in the sample above I couldn't do
exec sp_executesql 'dbcc checktable ('+@tablename+')'
Also the @sql (if you use one) has to be nvarchar or nchar .. and the string you pass in with the variable definition (parameter 2) has to have an N at before it to convert it to nvarchar.
Hope that help.
Ken
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
October 11, 2007 at 4:05 pm
Why do you want to use sp_executesql to execute the procedure?
October 12, 2007 at 5:37 am
Well, there are all the general reasons regarding security, because it is dynamic code. Reuse of execution plan doesn't enter into this because successive calls will access a different stored procedure, although it occurs to me that with enough traffic, execution plans might be reused under certain circumstances.
However, after some further fooling around this am, I came up with an EXEC call that would work, in the following format:
Declare @RequestID int,
@ErrorMessage varchar(250),
@UnDelimitedValue int,
@RowCount int,
@SubstitutionProcedureName varchar(250),
EXEC @SubstitutionProcedureName @RequestID = @RequestID, @ElementID = @UnDelimitedValue, @RowCount = @RowCount, @ErrorMessage= @ErrorMessage OUTPUT
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
October 12, 2007 at 6:09 am
I've excerpted the code I was trying to use with sp_executesql. And I hate to say that I'm not positive what changed fixed it, but it returns data now and does not error on the call. Guess I needed to heed the old advice to 'Shake your head, your eyes are stuck'.
Oh well. Thanks for everybody's attention.
Declare@RequestID int,
@ErrorMessage varchar(250),
@SubstitutionProcedureName varchar(250),
@SQLString nvarchar(500),
@ParmDefinition nvarchar(500),
@DynErrorMessage varchar(250),
@RowCount int
Set@SQLString = 'EXEC ' + @SubstitutionProcedureName + ' @RequestID = ' + Convert(varchar, @RequestID) + ', @ElementID = ' + Convert(varchar, @UnDelimitedValue) + ', @RowCount = @RowCount OUTPUT, @ErrorMessage = @ErrorMessage OUTPUT'
Set @ParmDefinition = N'@RequestID int, @ElementID varchar(4000), @RowCount int OUTPUT, @ErrorMessage varchar(250) OUTPUT'
EXEC sp_executesql @SQLString, @ParmDefinition, @RequestID = @RequestID, @ElementID = @UnDelimitedValue, @RowCount = @RowCount OUTPUT, @ErrorMessage = @ErrorMessage OUTPUT
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
October 12, 2007 at 8:13 am
I think this would be much simpler:
[font="Courier New"]Declare@RequestID int,
@ErrorMessage varchar(250),
@SubstitutionProcedureName varchar(250),
@SQLString nvarchar(500),
@ParmDefinition nvarchar(500),
@DynErrorMessage varchar(250),
@RowCount int
execute @SubstitutionProcedureName
@RequestID = @RequestID, @ElementID = @UnDelimitedValue, @RowCount= @RowCount OUTPUT, @ErrorMessage = @ErrorMessage OUTPUT[/font]
October 12, 2007 at 8:21 am
That is pretty much what I had when I replied to why I was using sp_executesql. Not only is it simpler, since the calls are strictly internal and post-data security audits, I don't think there is an advantage, in this case, to using sp_executesql
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply