| Groups | Blog | Home |
sql server programming : Execute stored proc dynamically (stored a variable)
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. Example DECLARE @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 = somethingelse OPEN myCursor FETCH NEXT FROM myCursor INTO @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.
Thanks so much for your quick response. I also like to pass the parameters to
the stored proc in a form of TABLE type variable, as I demo earlier. This is because it's a lot more flexible this way. Do you know of a way to do this? Thanks again. Calvin [quoted text, click to view] "Uri Dimant" wrote:
> Calvin > > > ---- > CREATE PROC myProc > @parameter1 VARCHAR(...), > @parameter2 INT > > AS > > CREATE TABLE #t > ( > spnames SYSNAME PRIMARY KEY > ) > > INSERT INTO #t VALUES ('sp1') > INSERT INTO #t VALUES ('sp2') > INSERT INTO #t VALUES ('sp3') > > SELECT 'EXEC '+spnames+' '''+@parameter1 +''''+','+cast(@parameter2 as > varchar(10)) FROM #t > > > > > > > > "Calvin KD" <CalvinKD@discussions.microsoft.com> wrote in message > news:93EFAF4B-C947-4A0D-A230-2C8CDE5CD418@microsoft.com... > > 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. > > > > Example > > DECLARE @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 = somethingelse > > OPEN myCursor > > FETCH NEXT FROM myCursor > > INTO @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 > >
Hi, Calvin
You cannot pass a table variable as a parameter. You should use a temporary table or a permanent (normal) table instead. If you expect that this procedure may be called simultaneously by more users, you can use @@SPID to separate parameters of different processes. For example: CREATE TABLE Parameters ( SPID smallint, ParamName varchar(100), ParamValue sql_variant, PRIMARY KEY (SPID,ParamName) ) GO CREATE PROCEDURE sp1 AS SELECT ParamValue FROM Parameters WHERE SPID=@@SPID AND ParamName='param1' GO CREATE TABLE tableOfStoredProcs ( StoredProcName sysname PRIMARY KEY ) INSERT INTO tableOfStoredProcs VALUES ('sp1') GO INSERT INTO Parameters (SPID, ParamName, ParamValue) VALUES (@@SPID, 'param1', 123) DECLARE @myStoredProc Varchar(100) DECLARE myCursor CURSOR LOCAL READ_ONLY FOR SELECT StoredProcName FROM tableOfStoredProcs --WHERE ... OPEN myCursor WHILE 1=1 BEGIN FETCH NEXT FROM myCursor INTO @myStoredProc IF @@FETCH_STATUS<>0 BREAK EXEC @myStoredProc END CLOSE myCursor DEALLOCATE myCursor DELETE Parameters WHERE SPID=@@SPID GO This usage of EXEC, without parentheses (i.e. "EXEC @ProcedureName"), is less vulnerable to SQL Injection attacks than using "EXEC (@SQLString)". Razvan
Thank you Razvan for your quick response. That's pretty much what i was
after. My original idea was to pass in a "data structure" as a parameter to the stored procs and then each stored proc picks out what it needs for processing. Anyway, it's a good start. Thanks. Calvin [quoted text, click to view] "Razvan Socol" wrote:
> Hi, Calvin > > You cannot pass a table variable as a parameter. You should use a > temporary table or a permanent (normal) table instead. If you expect > that this procedure may be called simultaneously by more users, you can > use @@SPID to separate parameters of different processes. > > For example: > > CREATE TABLE Parameters ( > SPID smallint, > ParamName varchar(100), > ParamValue sql_variant, > PRIMARY KEY (SPID,ParamName) > ) > GO > CREATE PROCEDURE sp1 AS > SELECT ParamValue FROM Parameters > WHERE SPID=@@SPID AND ParamName='param1' > > GO > CREATE TABLE tableOfStoredProcs ( > StoredProcName sysname PRIMARY KEY > ) > > INSERT INTO tableOfStoredProcs VALUES ('sp1') > > GO > INSERT INTO Parameters (SPID, ParamName, ParamValue) > VALUES (@@SPID, 'param1', 123) > > DECLARE @myStoredProc Varchar(100) > > DECLARE myCursor CURSOR LOCAL READ_ONLY FOR > SELECT StoredProcName FROM tableOfStoredProcs > --WHERE ... > > OPEN myCursor > > WHILE 1=1 BEGIN > FETCH NEXT FROM myCursor INTO @myStoredProc > IF @@FETCH_STATUS<>0 BREAK > > EXEC @myStoredProc > END > > CLOSE myCursor > DEALLOCATE myCursor > > DELETE Parameters WHERE SPID=@@SPID > GO > > This usage of EXEC, without parentheses (i.e. "EXEC @ProcedureName"), > is less vulnerable to SQL Injection attacks than using "EXEC > (@SQLString)". > > Razvan >
Calvin
---- CREATE PROC myProc @parameter1 VARCHAR(...), @parameter2 INT AS CREATE TABLE #t ( spnames SYSNAME PRIMARY KEY ) INSERT INTO #t VALUES ('sp1') INSERT INTO #t VALUES ('sp2') INSERT INTO #t VALUES ('sp3') SELECT 'EXEC '+spnames+' '''+@parameter1 +''''+','+cast(@parameter2 as varchar(10)) FROM #t [quoted text, click to view] "Calvin KD" <CalvinKD@discussions.microsoft.com> wrote in message news:93EFAF4B-C947-4A0D-A230-2C8CDE5CD418@microsoft.com... > 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. > > Example > DECLARE @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 = somethingelse > OPEN myCursor > FETCH NEXT FROM myCursor > INTO @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
Calvin
Read those articles http://www.sommarskog.se/dynamic_sql.html http://www.sommarskog.se/arrays-in-sql.html [quoted text, click to view] "Calvin KD" <CalvinKD@discussions.microsoft.com> wrote in message news:F367EEC4-4B38-4F21-836D-2ADBF2910C9B@microsoft.com... > Thanks so much for your quick response. I also like to pass the parameters > to > the stored proc in a form of TABLE type variable, as I demo earlier. This > is > because it's a lot more flexible this way. Do you know of a way to do > this? > Thanks again. > Calvin > > "Uri Dimant" wrote: > >> Calvin >> >> >> ---- >> CREATE PROC myProc >> @parameter1 VARCHAR(...), >> @parameter2 INT >> >> AS >> >> CREATE TABLE #t >> ( >> spnames SYSNAME PRIMARY KEY >> ) >> >> INSERT INTO #t VALUES ('sp1') >> INSERT INTO #t VALUES ('sp2') >> INSERT INTO #t VALUES ('sp3') >> >> SELECT 'EXEC '+spnames+' '''+@parameter1 +''''+','+cast(@parameter2 as >> varchar(10)) FROM #t >> >> >> >> >> >> >> >> "Calvin KD" <CalvinKD@discussions.microsoft.com> wrote in message >> news:93EFAF4B-C947-4A0D-A230-2C8CDE5CD418@microsoft.com... >> > 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. >> > >> > Example >> > DECLARE @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 = somethingelse >> > OPEN myCursor >> > FETCH NEXT FROM myCursor >> > INTO @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 >> >> >>
Calvin KD (CalvinKD@discussions.microsoft.com) writes:
[quoted text, click to view] > Thanks so much for your quick response. I also like to pass the > parameters to the stored proc in a form of TABLE type variable, as I > demo earlier. This is because it's a lot more flexible this way. Do you > know of a way to do this? Uri's suggestion is far too complex. Just say: EXEC @spname @par1, @par2, @par3 Uri suggested some aritcles on my web site, but not the one which appears to be the most pertinent to your problem, http://www.sommarskog.se/share_data.html. This article discusses techniques to share data between stored procedures. Unforunately, table variables cannot do that task. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Thank you all for your responses. I have certainly learnt a few new things.
As for my quest, I think I'll just have to pass the same number of parameters to each stored proc (even though some aren't used) unless we can come up with a better solution. The problem we're facing is that because we have a long list of stored procs (which will expand over the years) that we want to exec., and exactly which stored proc will be exec. depends on certain criteria for each year. Some will be "On" in one year and may be "Off" the next. Example: tblStoredProcs ========= StoredProcID StoredProcName Year Active Anyway, if anyone has an idea, please let me know. All suggestions are greatly welcomed. Cheers, Calvin [quoted text, click to view] "Erland Sommarskog" wrote:
> Calvin KD (CalvinKD@discussions.microsoft.com) writes: > > Thanks so much for your quick response. I also like to pass the > > parameters to the stored proc in a form of TABLE type variable, as I > > demo earlier. This is because it's a lot more flexible this way. Do you > > know of a way to do this? > > Uri's suggestion is far too complex. Just say: > > EXEC @spname @par1, @par2, @par3 > > Uri suggested some aritcles on my web site, but not the one which appears > to be the most pertinent to your problem, > http://www.sommarskog.se/share_data.html. This article discusses techniques > to share data between stored procedures. Unforunately, table variables > cannot do that task. > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Calvin KD (CalvinKD@discussions.microsoft.com) writes:
[quoted text, click to view] > Thank you all for your responses. I have certainly learnt a few new > things. As for my quest, I think I'll just have to pass the same number > of parameters to each stored proc (even though some aren't used) unless > we can come up with a better solution. It sounds like an excellent solution to me! After all, that is as close to the implemention of a the O-O concept of a virtual class you can come in T-SQL. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |