dynamicall execute SP without dynamic sql

[jw56578]

How would you go about dynamically executing a stored proc dependent on
a variable? I cannot use dynamic sql.

[Madhivanan]

Why do you want to do without dynamic sql?

Madhivanan

[Simon Hayes]

You can use a parameter instead of a proc name (see EXECUTE in Books
Online):

exec @p

But this is problematic if different procedures may require different
parameters. Or if the number of procedures is relatively small, then
you could just use IF ... ELSE ... to conditionally execute a proc.

Simon

[David Portas]

Use IF statements

IF @var = 'Proc1'
EXEC Proc1
IF @var = 'Proc2'
EXEC Proc2
....

If you create a new proc you just need to add it to the list. You could
even generate the list automatically from the info schema ROUTINES
view.

--
David Portas
SQL Server MVP
--

[jw56578]

That sounds like a interesting solution that would probably work. How
would you generate that list and then incorporate it into the if
statements. Thanks!

[David Portas]

Just query against the routine_name column and then cut and paste into
your SP. You still can't expect to automate that process entirely
without using dynamic SQL. Does that matter? Assuming you have adequate
change control procedures in place it shouldn't be a problem.

--
David Portas
SQL Server MVP
--