June 18, 2007 at 8:41 am
Can anyone explain the use of the number parameter when creating stored procedures ?
for example...
create procedure sp;1
begin
print 1
end
go
create procedure sp;2
begin
print 2
end
go
create procedure sp;3
begin
print 3
end
How do you execute the second of the sps ? Why would you use this ?? BOL talks about the ease of dropping several sps all at once, but maybe I'm missing something.
TIA
Mathew J Kulangara
sqladventures.blogspot.com
June 18, 2007 at 9:16 am
This is a way to have multiple versions of one procedure. For example if you create the three procedures below:
CREATE PROCEDURE dbo.test;1
AS
SELECT 1
GO
CREATE PROCEDURE dbo.test;2
AS
SELECT 2
GO
CREATE PROCEDURE dbo.test;3
AS
SELECT 3
GO
When you look at the proc in SSMS, you’ll only see the procedure named test. But if you script this procedure, or select Modify you will see all three versions shown with an ALTER statement.
Again, this allows you to have multiple versions of the same proc. Calling them is just as easy.
EXEC dbo.test;1
GO
EXEC dbo.test;2
GO
EXEC dbo.test;3
GO
Now whether or not you use this methodology is completely up to you. My personal opinion is that this will only confuse the guy who has to understand your code in the future, but I’m sure there are case where this versioning could be helpful.
One last note. You can drop all versions by using DROP PROCEDURE dbo.test or just a single instance by using DROP PROCEDURE dbo.test;2
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 18, 2007 at 9:26 am
Thanks, Jason.
Mathew J Kulangara
sqladventures.blogspot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply