Overloading Stored Procedures: Is it Possible? (2005)

[cmwright]

Hi all,

Does anyone know if it's possible to overload a stored procedure in SQL Server 2005 as you can with Sub Procedures in .NET programming languages? I was just thinking that it might be a useful concept in an enterprise architecture environment, but I haven't worked with 2005 yet.

-Chris

 

[vongrunt]

SQL is "stiff" strict-typing language, and stored procedures aren't methods or exposed properties of anything. I don't see the link here... correct me if I'm wrong. Hypothetical example perhaps?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[cmwright]

Sure.

Let's say that you have a suite of products that all feed off the same server. There are commonalities between some of the progams that allow them to use the same data and same stored procedures from one of the databases. Due to functional requirements, one of the programs will require a stored procedure to be expanded. Perhaps there are now more parameters that you'll need to provide or it handles business rules in a new fashion. However, the other programs will not be updated this quarter, and if you change the stored procedure, the other programs in the suite will break when trying to access the new stored procedure. In a situation like this, it would be useful to have two "versions" of the same procedure without having to create a new one, which is the obvious answer. However, if you could overload the stored procedure until the code for the other applications got patched, that would keep your environment much cleaner and easier to manage.

With .NET being an integral part of the 2005 environment now, I was wondering if anything besides TRY...CATCH had made it in.

Does that example make sense? I know that Oracle can do something like this via packages (so Google tells me), but I'm not familiar with that, either.

-Chris

 

[SQLDenis]

You can use optional parameters in the procedures

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[cmwright]

That would help in the case of parameters, but not if part of the business logic was different. I know tons of ways to work around the problem, but none that are quite as elegant as if stored procedures could be overloaded in 2005.

And like I said, this is 100% hypothetical. I'm still working on 2000.

-C

 

[vongrunt]

> That would help in the case of parameters, but not if part of the business logic was different.

OK, what if business logic is different but sproc signature (order and datatype of input args) is identical?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[cmwright]

Well, you couldn't overload it if that were the case. At least one would need to be different if indeed SQL Server 2005 is capable of overloading stored procedures.

I sort of feel like we're all trying to solve a problem that doesn't exist. Fun! But not really the point of the post, I guess. The "why" in this case could be any number of reasons. It's the "is it possible" that is the reason why I posted. I was hoping that an early adopter could scratch the itch that my brain created. Great comments and questions, though.

-C