Clicky

How do I create a Stored Procedure in SQL Server that has a dynamic name?
What I need is pass in a parameter 'SequenceName' to the name of the Stored Procedure.
The stored procedure will be [email protected] where @SequenceName is a parameter. If the value of @SequenceName is Action1, the name of the stored procedure will be SP_Action1 and so on.

Also, before creating the SP, I need to check if the SP exists. If it exists, I need to use the ALTER PROCEDURE script. If it does not exist, I need to use CREATE PROCEDURE script.

Thanks in advance!

asked 12/08/2011 11:43

spirose's gravatar image

spirose ♦♦


5 Answers:
You can use the below sntax to get the store procedure exisits are not

IF OBJECT_ID ( 'sp_'[email protected], 'P' ) IS NULL
    exec ' Dynamic Code'
GO

Try this, but not able to get your requirment in full
link

answered

santhimurthyd's gravatar image

santhimurthyd

Though there are ways it can be done and santhimurthyd suggestion is also nice, I couldn't see any use case of the action. It would be a great help if you explain why it is required?
link

answered 2011-12-08 at 21:01:32

TempDBA's gravatar image

TempDBA

The reason is because we will have different TSQL code wrapped in the SP depending on the SequenceName. So SP_Action1 will have different TSQL code than SP_Action2.

How do I create a Stored Procedure with name [email protected] ---this name will be different depending on what we pass as the value of the parameter @SequenceName...
link

answered 2011-12-08 at 21:24:52

spirose's gravatar image

spirose

Are you want to create store procedure dynamically else you wish to call the exisitng store procedure. In your comment that each sP will have different logic and how you want to create, like from an any programing language else the DB script for the production environement.

If youcan provide more info will helpful to trouble shoot else it be hard to provide suggestion.
link

answered 2011-12-08 at 21:43:46

santhimurthyd's gravatar image

santhimurthyd

dynamic name means dynamic sql:

1:
2:
3:
declare @proc_name varchar(100)
set @proc_name = 'SP_' + @sequencename
exec @proc_name


note that exec here must be WITHOUT brackets, as you want to pass a procedure name indeed.
exec(@procname) would fail, unless you did:
1:
2:
3:
declare @proc_name varchar(100)
set @proc_name = 'EXEC SP_' + @sequencename
exec(@proc_name)


hope this helps
link

answered 2011-12-08 at 23:05:49

angelIII's gravatar image

angelIII

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 12/08/2011 11:43

Seen: 274 times

Last updated: 12/09/2011 01:08