September 28, 2003 at 11:01 am
I need assistance executing stored procedures that are in a table.
For example,
SELECT sp_name
FROM dbo.stored_proc
WHERE sp_id = 1
* sp_id is the ID of the stored procedure in the table.
EXEC(@sp_name)
An example of a stored procedure name is:
dbo.testthis @year,@month
The stored procedures have varying parameters. I have tried numerous ideas, but I keep receiving the error message "Must declare variable @param_name. I need to code it this way otherwise I would need a ton of IF statements. Any help would be greatly appreciated.
September 28, 2003 at 1:51 pm
How else would it make sense to execute them anyway? I've had code in the past that would execute a proc from a list in a table, but the proc had a known set of params. In other words, it would look like this:
RowID Proc
1 usp_DoSomethingA ~OrderID~
When I go to call it, I'd parse for the different parameter tokens, in this case replacing ~OrderID~ with a real order Id. If you're using ADO, you can just execute it from the connection object without any other changes.
Andy
September 29, 2003 at 1:18 am
You can't use parameters in a stored procedure that you execute using dynamic SQL. The problem lies in the scope of the variables. Same problem when trying to return values from a procedure.
The only solution is to construct a table to store the parameters and retrieve them in your stored procedure, or parse the string like Andy proposes.
September 29, 2003 at 7:15 am
Thank you for your comments. I will use your solution.
September 29, 2003 at 7:31 am
Andy,
What you did is exactly what I need to do. I stated that there were varying amounts of parameters because I have more than one stored procedure.
For example,
SP_ID SP
1 dbo.usp_get_order @order_id
2 dbo.usp_display @order_id, @product
Do you have the SQL code for this?
Thank you for your help.
September 29, 2003 at 9:01 am
I used to have codes to do exactly the same thing. I think the key is to use the following syntax:
EXEC @ProcName @Parameter1, @Parameter2
Good luck!
YCH
October 1, 2003 at 11:47 am
Hi pop022,
for performance reasons I would suggest NOT to name your sprocs sp_*.
AFAIK SQL Server will always look first into master for sp_*
Just my $0.02
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 1, 2003 at 11:58 am
Frank is correct. Naming stored procedures not residing in master with sp_ as the first 3 characters will result in an SP:CacheMiss, a [COMPILE] lock, and a scan through a second time to try and find the execution plan. This effectively serializes the stored procedure because the [COMPILE] lock is exclusive.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
October 2, 2003 at 7:20 pm
I found a way around this issue. Thanks for the help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply