CODEWITHSUNDEEP
sql-serverstored-proceduresado.net
Life is more than this
Life is more than this

Reputation: 267

ADO.Net and stored procedure output parameters

In Ado.net, the code is calling a stored procedure with input and output parameters.

I understand that if some of the input parameters are optional (have default values in the SP), the code doesn't need to define and send the parameters values unless needed to.

My question is: Does the same apply to the optional output parameters? can the code ignore the optional (has a default value) SP output parameters?

I could have tested it myself but I don't have a working example right now, and I am short of time.

Thanks you.

Upvotes: 0

Views: 1719

Answers (2)

Jeremy Pridemore
Jeremy Pridemore

Reputation: 1995

EDIT: Turns out I was wrong here, but I'm going to leave my answer because the information on SqlParameter might be useful. Sorry for the inaccuracy though.

I don't believe so. You must send in an OUTPUT parameter and in ADO.NET this is accomplished by adding a SqlParameter with it's ParameterDirection property set to ParameterDirection.Output.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.direction.aspx

http://msdn.microsoft.com/en-us/library/system.data.parameterdirection.aspx

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294297

Yes. If a parameter has a default value then it may be safely omitted, irrelevant of the parameter direction (INPUT or OUTPUT). The fact that the procedure is called from ADO.Net is entirely irrelevant. Eg:

create procedure usp_test
    @a int = 1 output,
    @b int = 2
as
begin
    set @a = @b;
end 
go

exec usp_test

Whether is safe to do from a business rules point of view (ie. ignoring an OUTPUT parameter returned value), is entirely up to the specifics of the procedure and your app.

Upvotes: 3

Related Questions