471,108 Members | 1,312 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

dynamicall execute SP without dynamic sql

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

Jul 23 '05 #1
5 2295

Why do you want to do without dynamic sql?


Jul 23 '05 #2
You can use a parameter instead of a proc name (see EXECUTE in Books

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.


Jul 23 '05 #3
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

David Portas
SQL Server MVP

Jul 23 '05 #4
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!

Jul 23 '05 #5
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

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by PJ | last post: by
6 posts views Thread by Arti Potnis | last post: by
7 posts views Thread by JIM.H. | last post: by
2 posts views Thread by Damir | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.