Optional output parameters in stored 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. 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.

SQL Server optional input output parameters

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

Output parameters and default values do not work well together! This is from SQL 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. If you send in an uninitialized value i. NULL for the OUTPUT , you really got NULL inside the SP, and not 0. Makes sense, something got passed for that parameter.

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:. 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: IMHO this feature combination is a dubious construct I would consider a code smell phew!!

Seems like its poor in terms of readability since AddressId is intended strictly as an OUTPUT variable.

Stored Procedures - Output Parameters & Return Values - SQL Server Wiki - SQL Server - Toad World

Please let me know if you have a better solution. I was getting an System. 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. 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:. 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 Here's an example of nvl, which I think might address it a little better? By posting your answer, you agree to the privacy policy and terms of service. Stack Overflow Questions Developer Jobs Documentation beta Tags Users. Sign up or log in to customize your list. Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us.

Log In Sign Up. Join the Stack Overflow Community. Stack Overflow is a community of 7. Join them; it only takes a minute: Can I have an optional OUTPUT parameter in a stored procedure? Person Id Name Address Name Id FirstName LastName Address Id Country City Say I have a stored procedure that inserts a person.

Justin 4, 12 50 Both input and output parameters can be assigned defaults.

Using a Stored Procedure with Output Parameters | Microsoft Docs

Add a SET of the OUTPUT parameter in the SP like you're supposed to do , but do not set anything from the caller: Looks like I can just add a default value to the OUTPUT parameter such as: Matt Tester 2, 2 18 Adding on to what Philip said: I had a stored procedure in my sql server database that looked like the following: So it now looks as the following: JABFreeware 2, 2 16 A Kimmel 6.

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: StoredProcedure; Taken from here.

Daniel Hoffmann-Mitscherling 1, 1 8 That doesn't answer the question. I'm using the Enterprise Library's db. GetStoredProcCommand which will do that for me. I have called many stored procs in this way and they work quite well.

AppendInsertPersonParameters db, insertPersonCommand ; db. I edited to be a little more relevant. Sign up or log in StackExchange. Sign up using Facebook.

Sign up using Email and Password. Post as a guest Name. Stack Overflow works best with JavaScript enabled. MathOverflow Mathematics Cross Validated stats Theoretical Computer Science Physics Chemistry Biology Computer Science Philosophy more 3. Meta Stack Exchange Stack Apps Area 51 Stack Overflow Talent.