Since SQL Server doesn't have packages, what do programmers do to get around it?

Question

I have SQL SERVER database that have a huge proliferation of stored procedures. In my Oracle databases it's not a problem because Oracle supports packages.

Even if SQL Server didn't support the cool features of packages like State and encapsulation, what do programmers do to get around this obvious deficiency?

Answer 1

Come up with a good naming convention, use it, and enforce it.

Answer 2

While SQL Server has nothing to offer by way of the "cool features" of encapsulation and package state like you are used to, you can organize your stored procedures into schemas.

In enterprise manager, these procs are still all listed together which makes for a HUGE treelist if you have hundreds of procs. I too miss the organization and cool features of Oracle packages. However, all platforms have their strengths.

NOTE: Writing stored procedures in the .NET language DOES give you encapsulation and state. It still does not however separate them in the EM treeview in any special way.

Answer 3

Schemas may be used to organize stored procedures and other objects. Personally, I prefer to use schemas when they organize objects by functional area, and where those funcational areas correspond to security boundaries. An example of this is found in the AdventureWorks sample databse, which has schemas like "HumanResources" and "Sales". The theory being that a given user may need access to objects in "HumanResources", but may not need access to "Sales" information.

An alternative is to use a naming convention and enforce it, as James says above. I'll add that SQL Server Management Studio has a filter button that can be used to filter the list of objects displayed. For instance, one can click on the "Stored Procedures" folder and filter on Name contains "Add".

On my current project, I have pulled a number of SQL queries out of SSIS packages and into stored procedures. In order to distinguish between these stored procedures and those that should be of general use, I have prefixed the names with "ssis". It would certainly have been more pleasant if I could have created something similar to a namespace in C# or C++, and created "SSIS.SelectUserLookupData" instead of "ssis_SelectUserLookupData". It would be even nicer if these namespaces could be nested.

If this is one of the featues of Packages in Oracle, then perhaps someone would let me know.

Answer 4

I would thank my lucky stars that SQL Server doesn't have packages. Oracle packages suck.

Hmm, we need a way to take all these procedures and put them in one place. I know! Let's make developers create and maintain two files for each package. They will love us forever!

As long as MS never implements packages like Oracle did, it'll be a win in my book.

EDIT for commenters:

Oracle Packages are simply a way to organize your stored procedures into, well, packages so that you don't have 100 stored procedures sitting around, but maybe 5 packages. They're not stackable like packages in Java or C# code. All packages are at the same level.

A package requires two files: the headers file and the body file. This creates frustration when adding new procedures to an existing package, because you cannot add the body without adding the header, even though it contains the exact same information as is in the body.

For example, here is a snippet from the header file of one of my packages:

    PROCEDURE bulk_approve_events
(
 i_last_updated_by IN VARCHAR2,
 o_event OUT NUMBER
);

And here's the corresponding procedure in the body:

    PROCEDURE bulk_approve_events
(
 i_last_updated_by IN VARCHAR2,
 o_event OUT NUMBER
) IS
...
BEGIN
...
END;

No difference. The header file is useless and is simply another hurdle for the developer to step over when developing with packages. On my project, we have a convention that all the commented documentation for each procedure goes in the header, along with the details of when it was added and by whom, but that could just as easily be included in the body.

Answer 5

I've worked with both SQL Server and Oracle so have seen the good and bad of both. As the above comments have beena bit heated I'll try and keep this as neutral as possible...

So, what's an Oracle Package? Think of it like a database class

The Package has two elements: a header file and a body file. The header file is your public interface, and contains the signature (name, params and return type if applicable) of all the stored procedures or functions (in Oracle a function returns a value, a stored proc doesn't) that are directly callable. The package body must implement all the procedure signatures in the package header file.

The body element of the package contains all the stored procs and logic that actually do the work. You may have a Save procedure declared in the package header that calls an insert or update proc that exists in the body. The developer can only see the "Save" proc. It's important to keep in mind that the package body can also implement procs or functions not declared in the package header, they're just not accessible outside of the package itself.

I found packages to be really useful for a number of reasons:

1. You've got the concept of a public interface that can be provided to other developers
2. Packages can mirror your compiled classes. My Orders.Save() C# method will call my Oracle Orders.SaveLineItem method to save each line item and an Oracle SaveOrder method to save the order summary details.
3. My procs are grouped together in a nice, logical way inside the packages

Personally, I would be love MS to implement some kind of package functionality as I think it makes for a cleaner database.