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.
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.
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.
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.
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.
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
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!!)
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.
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)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With