Execute stored proc dynamically (stored a variable)

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 selecte
d
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 for
m
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.
CalvinCalvin
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|||Thanks so much for your quick response. I also like to pass the parameters t
o
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, 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
Read those articles
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/arrays-in-sql.html
"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:
>|||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
"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 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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 parameter
s
to each stored proc (even though some aren't used) unless we can come up wit
h
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
"Erland Sommarskog" wrote:

> Calvin KD (CalvinKD@.discussions.microsoft.com) writes:
> 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 technique
s
> 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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Calvin KD (CalvinKD@.discussions.microsoft.com) writes:
> 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx