.NET Force :: Grouping Stored Procedures

Thursday, September 02, 2010

Search:

Article

48Hrs Help PAQ's

FAQ's

Download

Books

MS Alerts

Write to Us

Feedback

Link

	a)	Why use .NET My Services?

	b)	What are .NET My Services?

Get This Blog via Email:

Grouping Stored Procedures

By Vadivel Mohanakrishnan
September 10, 2004
Page is Viewed 5097 times

VadiVel

Almost always during our testing or R&D we might need to create some stored procedure(s) which would be deleted later. In my experience I have learnt that it’s really a pain to create stored procedures with different fancy names and later delete each one without fail.

What I meant is this?

Create procedure sampleSP_Authors
As
Select au_id, au_lname, au_fname from authors
Go

Create procedure sampleSP_AuthorsALL
As
Select * from authors
Go

If we need to delete these SP’s, created for testing purpose within PUBS database, we need to call that many drop proc statements like,

Drop Procedure sampleSP_Authors
Drop Procedure sampleSP_AuthorsALL

and so on ...

Work around

That said; let us now see an interesting working around in SQL Server for this.

Create procedure sampleSP_Authors;1
As
Select au_id, au_lname, au_fname from authors
Go

Create procedure sampleSP_Authors;2
As
Select * from authors
Go

Pay attention to "; integer value" after the procedure name. We can use the same procedure name with different numbers to group procedures (of same name) like shown in the above samples.

This way for testing or R&D purpose we can keep a common name to ALL procedures and just change the integer value alone. In this way, it’s enough if we just call "Drop Procedure sampleSP_Authors" - it would delete a group of stored procedure with a single drop procedure statement.

For executing the SP sampleSP_Authors;2 use the below syntax:

Exec sampleSP_Authors;2

Note: Using "ProcedureName;1" and "ProcedureName" alone are one and the same.

i.e.,

Exec sampleSP_Authors;1
Exec sampleSP_Authors

Both the above statement would return the same result only. To understand it better try to alter the SP sampleSP_Authors;1 using the below code snippet:

Alter Proc sampleSP_Authors
As
Select au_id, city,state,zip from authors

Now if we run "Exec sampleSP_Authors;1" it would return the result containing au_id, city, state and zip from authors table. That’s cool isn’t it?

Conclusion

In this article we saw a cool feature of SQL Server for grouping stored procedures of same name. Though this can be used in testing or R&D environment, I have never seen a real use of it in production environment. Hope this article was useful to you.

If you need any further clarification on this or do you have any SQL doubt to be clarified does get in touch with me (http://vadivel.thinkingms.com or vmvadivel@gmail.com). Obviously I won’t guarantee that I would provide a solution in all cases but surely would try my best J

Class:
A reference type that encapsulates data (constants and fields) and behavior (methods, properties, indexers, events, operators, instance constructors, static constructors, and destructors), and can contain nested types. Class types support inheritance, a mechanism whereby a derived class can extend and specialize a base class. See also: encapsulation, indexer, property, reference type.

SQL Server:
Microsoft SQL Server is a set of components that work together to meet the data storage and analysis needs of the largest Web sites and enterprise data processing systems.