Can I have an optional OUTPUT parameter in a stored procedure?

I have a stored procedure that has a bunch of input and output parameters because it is Inserting values to multiple tables. In some cases the stored proc only inserts to a single table (depending on the input parameters). Here is a mocked up scenario to illustrate.

Tables / Data Objects:

Person

Id
Name
Address

Name

Id
FirstName
LastName

Address

Id
Country
City

Say I have a stored procedure that inserts a person. If the address doesn't exist I won't add it to the Address table in the database.

Thus when I generate the code to call the stored procedure I don't want to bother adding the Address parameter. For INPUT parameters this is ok because SQL Server allows me to supply default values. But for the OUTPUT parameter what do I do in the stored procedure to make it optional so I do not receive an error...

Procedure or function 'Person_InsertPerson' expects parameter '@AddressId', which was not supplied.

Solution 1:

Both input and output parameters can be assigned defaults. In this example:

CREATE PROCEDURE MyTest
  @Data1 int
 ,@Data2 int = 0
 ,@Data3 int = null output

AS

PRINT @Data1
PRINT @Data2
PRINT isnull(@Data3, -1)

SET @Data3 = @Data3 + 1

RETURN 0

the first paramter is required, and the second and third are optional--if not set by the calling routine, they will be assigned the default values. Try messing around with it and the following test-call routine in SSMS using different values and settings to see how it all works together.

DECLARE @Output int

SET @Output = 3

EXECUTE MyTest
  @Data1 = 1
 ,@Data2 = 2
 ,@Data3 = @Output output

PRINT '---------'
PRINT @Output

Solution 2:

Output parameters and default values do not work well together! This is from SQL 10.50.1617 (2008 R2). Do not be fooled into believing this construct magically does a SET to that value on your behalf (like my co-worker did)!

This "toy" SP interrogates the OUTPUT parameter value, whether it is the default value or NULL.

CREATE PROCEDURE [dbo].[omgwtf] (@Qty INT, @QtyRetrieved INT = 0 OUTPUT)
AS
IF @QtyRetrieved = 0
BEGIN
    print 'yay its zero'
END
IF @QtyRetrieved is null
BEGIN
    print 'wtf its NULL'
END
RETURN

If you send in an uninitialized value (i.e. NULL) for the OUTPUT, you really got NULL inside the SP, and not 0. Makes sense, something got passed for that parameter.

declare @QR int
exec [dbo].[omgwtf] 1, @QR output
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

output is:

wtf its NULL
@QR=NULL

If we add an explicit SET from the caller we get:

declare @QR int
set @QR = 999
exec [dbo].[omgwtf] 1, @QR output
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

and the (unsurprising) output:

@QR=999

Again, makes sense, a parameter is passed, and SP took no explicit action to SET a value.

Add a SET of the OUTPUT parameter in the SP (like you're supposed to do), but do not set anything from the caller:

ALTER PROCEDURE [dbo].[omgwtf] (@Qty INT, @QtyRetrieved INT = 0 OUTPUT)
AS
IF @QtyRetrieved = 0
BEGIN
    print 'yay its zero'
END
IF @QtyRetrieved is null
BEGIN
    print 'wtf its NULL'
END
SET @QtyRetrieved = @Qty
RETURN

Now when executed:

declare @QR int
exec [dbo].[omgwtf] 1234, @QR output
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

the output is:

wtf its NULL
@QR=1234

This is the "standard" behavior for OUTPUT parameter handling in SPs.

Now for the plot twist: The only way to get the default value to "activate", is to not pass the OUTPUT parameter at all, which IMHO makes little sense: since it's set up as an OUTPUT parameter, that would mean returning something "important" that should be collected.

declare @QR int
exec [dbo].[omgwtf] 1
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

gives this output:

yay its zero
@QR=NULL

But this fails to capture the SPs output, presumably the purpose of that SP to begin with.

IMHO this feature combination is a dubious construct I would consider a code smell (phew!!)

Solution 3:

Looks like I can just add a default value to the OUTPUT parameter such as:

@AddressId int = -1 Output

Seems like its poor in terms of readability since AddressId is intended strictly as an OUTPUT variable. But it works. Please let me know if you have a better solution.

Solution 4:

Adding on to what Philip said:

I had a stored procedure in my sql server database that looked like the following:

dbo.<storedProcedure>
(@current_user char(8) = NULL,
@current_phase char(3) OUTPUT)

And I was calling it from my .net code as the following:

 DataTable dt = SqlClient.ExecuteDataTable(<connectionString>, <storedProcedure>);

I was getting an System.Data.SqlClient.SqlException: Procedure or function expects parameter '@current_phase', which was not supplied.

I am also using this function somewhere else in my program and passing in a parameter and handling the output one. So that I didn't have to modify the current call I was making I just changed the stored procedure to make the output parameter also optional.

So it now looks as the following:

dbo.<storedProcedure>
(@current_user char(8) = NULL,
@current_phase char(3) = NULL OUTPUT)