Can I have an Optional OUTPUT parameter in a SQL Stored Procudure? (MS SQL Server 2008)

Question

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
Person.Id
Person.Name
Person.Address

Name
Name.Id
Name.FirstName
Name.LastName

Address
Address.Id
Address.Country
Address.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 db. Thus when I generate the code to call the stored procedure I don't want to bother adding the Address parameters. For INPUT parameters this is ok because SQL allows me to supply default values. But for the OUTPUT parameter what do i do in the SQL Stored Procedure to make it optional so I do not recieve an error...

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

Answer 1

Since you are executing a stored procedure and not a SQL statement, you have to set the command type of your SQL Command to Stored Procedure:

cmd.CommandType = CommandType.StoredProcedure;

Taken from http://www.eggheadcafe.com/community/aspnet/2/10146343/procedure-or-function-.aspx

Also, once you get that error removed, you can use SQL's nvl() function in your procedure to specify what you want displayed when a NULL value is encountered.

Sorry about not properly addressing the question...must have misunderstood you. Here's an example of nvl, which I think might address it a little better?

select NVL(supplier_city, 'n/a') from suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.

Answer 2

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.

Thanks.

Answer 3

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