Sql-server – have an optional OUTPUT parameter in a stored procedure

data-accesssql-serverstored-procedures

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.

Best Solution

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
Related Question