SQL Server output parameter issue

I can give you short example on how to create stored procedure with output parameter.

CREATE PROCEDURE test_proc

@intInput int,
@intOutput int OUTPUT

AS
set @intOutput = @intInput + 1 

go

And to call this procedure and then use output parameter do as following:

declare @intResult int
exec test_proc 3 ,@intResult OUT
select @intResult

You see, that you should declare ouput variable first. And after executing stored procedure, the output value will be in your variable. You can set any value to your output variable, but after executing stored procedure it will contain exactly what stored procedure return (no matter what value was in the output variable).

For example:

declare @intResult int
exec test_proc 3 ,@intResult OUT
select @intResult

It will return 4. And:

declare @intResult int
set @intResult = 8
exec test_proc 3 ,@intResult OUT
select @intResult

Also return 4.

Yes, you can use an OUTPUT parameter for both passing in and retrieving values (although I can't think of a good reason to do that at the moment).

Here's a trivial example that demonstrates this:

-- The stored procedure
CREATE PROCEDURE OutParamExample
    @pNum int OUTPUT
AS
BEGIN
    select @pNum
    set @pNum = @pNum + 5
END
GO

-- use a local variable to retrieve your output param value
declare @TheNumber int
set @TheNumber = 10

print @TheNumber
exec OutParamExample @TheNumber OUTPUT
print @TheNumber

The results will look like this:

10

-----------
10

(1 row(s) affected)

15

EDIT: OK, I think I missed a "not" in the second paragraph and may not have answered the question you asked. If you want a strict output parameter (e.g. something like a return code), you certainly don't have to provide a value to the local variable passed as the output parameter, but you still have to declare that local variable so that you'll have a way of accessing the returned value outside of the scope of the procedure itself.

For example:

declare @LocalNumber int
-- I don't have to assign a value to @LocalNumber to pass it as a parameter
exex OutParamExample @LocalNumber OUTPUT  
-- (assume SP has been altered to assign some reasonable value)

-- but I do have to declare it as a local variable so I can get to
-- the return value after the stored procedure has been called
print @LocalNumber

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