Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server output parameter issue

I am using SQL Server 2008 Enterprise. I am learning OUTPUT parameter of SQL Server stored procedure. For example, stored procedure sp_add_jobschedule has an OUTPUT parameter called schedule_id.

http://msdn.microsoft.com/en-us/library/ms366342.aspx

My confusion is, looks like OUTPUT parameter could be provided an input value and also returns a value, looks like it has behaviors of both INPUT and OUTPUT parameter? Is it allowed not to provide any INPUT values for OUTPUT parameter (to make it look like pure output parameter behavior)?

like image 781
George2 Avatar asked Aug 08 '09 07:08

George2


People also ask

What is an output parameter SQL?

Output parameter is a parameter whose value is passed out of the stored procedure/function module, back to the calling PL/SQL block. An OUT parameter must be a variable, not a constant. It can be found only on the left-hand side of an assignment in the module.

Where are output parameters used in SQL Server?

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.


1 Answers

The confusion is justified to a degree - and other RDBMS like Oracle do have stored procedure parameters which can be of type IN (input only), OUT (output only), or INOUT (both ways - "pass by reference" type of parameter).

SQL Server is a bit sloppy here since it labels the parameter as OUTPUT, but really, this means INPUT/OUTPUT - it basically just means that the stored proc has a chance of returning a value from its call in that parameter.

So yes - even though it's called OUTPUT parameter, it's really more of an INPUT/OUTPUT parameter, and those IN, INOUT, OUT like in Oracle do not exist in SQL Server (in T-SQL).

like image 108
marc_s Avatar answered Sep 17 '22 18:09

marc_s