Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 331
Justin Avatar asked Jul 16 '10 17:07

Justin


People also ask

Can a stored procedure have optional parameters?

How to create stored procedure that accepts optional parameter in SQL Server? To create optional parameter in stored procedure, we set the parameter value to NULL while creating a stored procedure.

Can stored procedure have output parameter?

The Output Parameters in Stored Procedures are used to return some value or values. A Stored Procedure can have any number of output parameters. The simple logic is this — If you want to return 1 value then use 1 output parameter, for returning 5 values use 5 output parameters, for 10 use 10, and so on.

Can procedure have zero or more output parameters?

A stored procedure can have many output parameters. In addition, the output parameters can be in any valid data type e.g., integer, date, and varying character. Second, after the SELECT statement, we assigned the number of rows returned by the query( @@ROWCOUNT ) to the @product_count parameter.

What is optional parameter in stored procedure?

A parameter is considered optional if the parameter has a default value specified when it is declared. It is not necessary to provide a value for an optional parameter in a procedure call. The default value of a parameter is used when: No value for the parameter is specified in the procedure call.


5 Answers

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
like image 111
Philip Kelley Avatar answered Nov 14 '22 02:11

Philip Kelley


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!!)

like image 34
escape-llc Avatar answered Nov 14 '22 02:11

escape-llc


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.

like image 23
Justin Avatar answered Nov 14 '22 02:11

Justin


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)
like image 39
A Kimmel Avatar answered Nov 14 '22 03:11

A Kimmel


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 here.

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.

like image 20
rownage Avatar answered Nov 14 '22 01:11

rownage