Overloaded Constructor for SQL Server stored procedure?

Question

I'm making a stored procedure and I want it to accept a different number of parameters and have different behavior based on the number of parameters supplied. So let's say if I supply one parameter (for example a number) it returns the number. If I supply it with two numbers, I want it to add them together. If I supply it with a string, and a number I want it to concatenate the string with the number and display it--Similar to the behavior of overloaded constructors in Java.

Is there such a functionality with Stored Procedures in SQL Server, or am I better off just creating separate stored procedures. I only wanted to go this route to make my application structure simpler.

Thanks

Answer 1

<TL;DR>

1. Is overloading supported in SQL Server: Nope. But even if it was, you would likely have issues related to source control and/or debugging.

2. Can a subset of parameters be passed in, altering the behavior: Yep, but you will have a single piece of code that won't be easy to work with or maintain.

Conclusion: Just create one Stored Procedure or Function per behavior. It will be:

• easier to work with via both T-SQL and app code
• easier to maintain (hence less error-prone)
• easier to debug
• easier to integrate into source control
• at least one thing you won't be blamed for after you leave or change teams / departments ;-)

---

There seems to be some confusion regarding how to interpret this Question with regards to the ultimate goal, and assumptions are being made on both sides, so let's start by clarifying what we are actually talking about.

Overloading

Overloading consists all, not some, of the following:

1. Code separation: Completely separate functions / methods.

2. Exact same function / method name for these separate pieces of code.

3. Unique number of, and/or combination of datatypes for, input parameters.

4. Execution of the shared name routes to the proper function / method based upon the signature -- the unique variation of input parameters.

5. Each separate function / method of the same name but differing input parameters can have a different datatype for the return value (note: this is merely an ability, not a requirement like the items noted above).

With this in mind, overloading is not possible (to any degree) in SQL Server, either for Stored Procedures or Functions (Scalar or TVF). Stored Procedures and Functions must have unique names (at least within a particular Schema). However, this restriction is just for user-defined objects as quite a few built-in Functions make use of overloading.

SQL Server does have "numbered" Stored Procedures (as pointed out in @Aaron's answer), but these should not be confused with overloads (not even partially) because they are missing everything that makes overloads different from those same, separate pieces of code having unique names:

1. While the "base" name of the Stored Procedure can be the same, this base name cannot be used to reference the code for execution; you are required to include the "number" (well, for ;1 it is optional), making it no different than having a vastly different name. Meaning, executing that name without any ;<number> following the name will always route to ProcName;1. Hence the "number" is actually part of the name.

   In fact, the only time you can refer to the group without using the ;<number> syntax is in a DROP PROCEDURE statement, and then that is the only way to reference the group. Meaning, you cannot DROP PROCEDURE ProcName;3 but instead can only drop all of them.

2. The variations of input parameters are not required to be unique, as shown in this example:

   CREATE PROCEDURE #NumberedProc (@Input INT)
   AS
   SELECT @Input AS [inp];
   GO
   CREATE PROCEDURE #NumberedProc;2 (@Input INT)
   AS
   SELECT @Input * 2 AS [h], 2 AS [g];
   GO
   
   EXEC #NumberedProc 55;
   
   EXEC #NumberedProc;2 55;
   

3. Automatic routing to the appropriate module is not available, because it cannot be available due to the signatures being allowed to be the same. Hence the version number is required when executing.

True overloading is allowed in PostgreSQL for Functions, and it does work, but it also causes more pain than it is worth. It did, to a small degree, simplify the code from the app layer perspective, but it was very difficult to both manage in source control (assuming each object has a script named for the name of the object) as well as debug (you had to hunt down the particular signature from the error message). We eventually did away with the overloads and made every unique signature have a unique object name ;-).

Passing in a subset of parameters resulting in different behavior

You are allowed to have optional parameters that have default values (and the default can be NULL) for both Stored Procedures and Functions, but this is still a single Stored Procedure or Function. Meaning:

1. There is no separation of code; you will need a series of IF statements to properly route to the desired functionality. Assuming that the default values are set to NULL, you would be checking for which input parameters are NOT NULL.

2. Return types:

   1. Scalar Functions can only have one return datatype. You could return SQL_VARIANT to avoid a specific return type, but there are issues with this approach: performance degradation and LOB types are not supported (i.e. VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), and XML).

   2. Table-Valued Functions can only have one return type, and it is determined at compile time.

   3. Stored Procedures do allow for defining the result set structure at run-time, but that would be a very confusing interface to code to in the app layer.

   4. Stored Procedures also allow for OUTPUT parameters, but you would need at least one per each datatype that you will be returning, and the calling code would need to know which one(s) to check based on which input parameters are being passed in. This would also be a confusing interface to code to.

Because this is a single object, you don't have the management and debugging issues like you would with true overloads, but you now have a large, messy, convoluted Stored Procedure or Function that is also hard to maintain, just for different reasons.

It would be something like:

CREATE PROCEDURE dbo.DoTooMuchInOneProcedure
(
  @Int1 INT = NULL,
  @Int2 INT = NULL,
  @String1 NVARCHAR(50) = NULL,
  @IntOut INT = NULL OUTPUT,
  @StringOut NVARCHAR(50) = NULL OUTPUT
)
AS
SET NOCOUNT ON;

IF (@Int1 IS NOT NULL AND @Int2 IS NULL AND @String1 IS NULL)
BEGIN
  SET @IntOut = @Int1;
  RETURN;
END;

IF (@Int1 IS NOT NULL AND @Int2 IS NOT NULL)
BEGIN
  SET @IntOut = @Int1 + @Int2;
  RETURN;
END;

IF (@Int1 IS NOT NULL AND @String1 IS NOT NULL)
BEGIN
  SET @StringOut = @String1 + CONVERT(NVARCHAR(50), @Int1);
  RETURN;
END;

Answer 2

Well, you can kind of do this with numbered procedures, but you do have to change the calling code a little bit.

CREATE PROCEDURE dbo.foo;1
  @a INT
AS
  SELECT @a;
GO

CREATE PROCEDURE dbo.foo;2
 @a INT, @b INT
AS
  SELECT @a + @b;
GO

EXEC dbo.foo @a = 1;

EXEC dbo.foo;2 @a = 1, @b = 2;
------------^^ have to specify any overload other than ;1

However, numbered procedures are deprecated, and they mess with lots of tools and built-ins. Highly not recommended even if you're stuck on an ancient version of SQL Server.

Just create separate procedures. Much easier to manage, and since the calling code has to know whether to pass one input or two anyway, it is just as easy to make the procedure name variable.

Answer 3

As other answers have noted, overloading is not supported for SQL Server stored procedures. One workaround could be having your stored procedure accept an xml variable as parameter, so your application could send a "set" of arguments in an xml blob:

CREATE PROCEDURE dbo.someproc
    @params xml
AS

SELECT
    tbl.n.value('@value', 'varchar(max)') AS val
FROM @params.nodes('/params/param') AS tbl(n)

To excercise control over the type or number of arguments passed, you can bind your xml variable to an xml schema.

CREATE XML SCHEMA COLLECTION myschema
AS '<xsd:schema...

... and to declare an xml variable as typed,

CREATE PROCEDURE dbo.someproc
    @params xml(myschema)
AS

Answer 4

I do mine differently

Let us say we want a stored proc to get customer orders for a specific date range, or specific day or no specific range or for all customers. This can be accomplished by three parameters (@startdate datetime,@enddate datetime,@customerid int)

With this, if you pass null, null, null or '','','' it will show all customer orders for all dates If you put in 05/1/2020,null,null it will give you all orders for that specific date for all customers

If you put 5/1/2020,05/31/2020,null it will give you all orders for that date range for all customers

If you put in customerid and no dates, it will give you all orders for that customer

As you can see, there are more variants I handle such as start date, null, customerid value or start/end/customerid

How do I do this, I read in the values, have iif statements to validate dates, etc., and then assign the where clause to a varchar(4000)

Then for the rest of the SQL, I set another varchar = sql + the dynamic variable and then exec (@mysql)

The query is the same, only change the where clause. Th e where clause is defined based on input. In fact, I even do smart checking like the start date < end date, that the dat is really a date with isdate(), etc.

This is yet another option that works well and fast.