In SQL Server, is an OUTPUT parameter actually an INPUT/OUTPUT parameter? I'm assuming this since even if we specify the OUTPUT keyword for a parameter in the stored procedure definition, we are still required to supply value for this parameter when calling a stored procedure.
Asked
Active
Viewed 8,859 times
1
marc_s
- 732,580
- 175
- 1,330
- 1,459
user702769
- 2,435
- 2
- 25
- 34
-
yes, it is in-out, but you can easily test it out on your own. – KM. Jan 04 '12 at 18:50
3 Answers
2
Yes, you have to provide a value for the output parameter.
For example, of you create a stored procedure like this:
CREATE PROC sales_for_type @type VARCHAR(55), @total_sales INT OUTPUT
AS
SELECT SUM(qty) FROM sales a, titles b
WHERE
a.title_id = b.title_id
and
b.type = @type
and then you call it like:
DECLARE @total_sales_business int
EXEC sales_for_type business, @total_sales=@total_sales_business OUTPUT
Check this article.
TheBoyan
- 6,802
- 3
- 45
- 61
-
I assume when we call sales_for_type via "EXEC sales_for_type business, @total_sales=@total_sales_business OUTPUT", the value passed to @total_sales is Null?! Anyways, thank you both for your help – user702769 Jan 04 '12 at 19:13
0
Yes it is both input and output as others already answered. But in case you don't want to be forced to give an input parameter, you can easily set a default value in the parameter declaration.
See also this question: Can I have an optional OUTPUT parameter in a stored procedure?
