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

user_103203 0
Viewed 93186

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.

data-access stored-procedures sql-server
5 Answers

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)
user_25280270
Related