how to create collection of stored procedures / functions

Question

how to create collection of stored procedures / functions

NewBie-396809

SSC Journeyman

Points: 88
More actions
January 30, 2007 at 4:22 am

#171195

Hi all,
I am newbie to sql server 2000.
I am looking for some kind of method where i can create, say a library, of stored procedures / functions. A very similar to Oracle where i can define a package wherein i can define procedures / functions and can call any one of them whenever required.
I may sound absurd but any suggestions / solution, if any one can provide????
Thanks in advance.
NewBie

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

sql server does not have something like package in oracle. but u can nest your stored procedures upto a limit if 32 procedures called within a single procedure. u have to create ur procedures and functions separately in sql server.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 2

In SQL Server each stored procedure is like your own function and they can call each other, but htey are not "grouped" together in anyway. Instead you've had to use naming conventions to keep them organized.

NewBie-396809 SSC Journeyman Points: 88 More actions · Answer 3

Hi all,

Thanks for the reply.

Actually, I was thinking in terms of creating some kind of library of stored procedures / functions which are very frequently used and related to each other so that they can be traced easily.

Anyway, Thanks to all.

Regards,

NewBie.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 4

Actually... there is a way... if you look in Books OnLine, the syntax for creating a stored procedure is....

CREATE PROC [ EDURE ] procedure_name [ ; number ]

[ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ]

] [ ,...n ]

[ WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

It's not quite the same as an Oracle package but if you say DROP PROCEDURE without the ;Number notation, all procedures of the same name will be dropped kinda like an Oracle package.

Read up on it in Books Online... might be close enough, might not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)