Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-01-24 : 08:39:35
|
| Calvin writes "Hi,Can someone please show me how to execute a stored proc which has been assigned to a variable. I have a table of stored procedures and I want to pick out a certain sp, based on a set of criteria, and execute it dynamically. I'm thinking of setting up a CURSOR to loop through the selected sp, assign each one to a variable and then execute it but I don't know how yet.I would also like to know how to pass a variable of type TABLE to the above stored proc. This table type variable contains a list of parameters in a form of "key-value" pairs.ExampleDECLARE @myTable TABLE( paramName Varchar(100), paramValue Varchar(100))INSERT INTO @myTable(paramName, paramValue)VALUES ('param1', '123')DECLARE @myStoredProc Varchar(100)DECLARE myCursor CURSOR FOR SELECT StoredProcName FROM tableOfStoredProcs WHERE something = somethingelseOPEN myCursor FETCH NEXT FROM myCursorINTO @myStoredProc... EXEC @myStoredProc(@myTable) -- this is what i want to do but syntactically incorrect.I'm using SQL Server 2000.Any suggestion is greatly appreciated.Calvin" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Execute stored proc dynamically (stored as a variable) 