Passing a value into a Stored Procedure using an OUTPUT parameter

Question

Post reply

Passing a value into a Stored Procedure using an OUTPUT parameter

SW_Lindsay

SSCommitted

Points: 1983
More actions
May 31, 2011 at 1:10 pm

#238280

I created a stored procedure.

CREATE PROCEDURE PR_SWL_TEST

@X integer OUTPUT

AS

Print @X

Select @X = 10 where 1=1

Print @X

Return @X

When I run it like this

DECLARE @rc int

DECLARE @X int

-- TODO: Set parameter values here.

Set @X = 2

EXECUTE @rc = [excelleRx_DBA].[dbo].[PR_SWL_TEST]

@X = 2 OUTPUT

Print @X

GO

I get this error

Msg 179, Level 15, State 1, Line 9

Cannot use the OUTPUT option when passing a constant to a stored procedure.

When I run it like this, it works

DECLARE @rc int

DECLARE @X int

-- TODO: Set parameter values here.

Set @X = 2

EXECUTE @rc = [excelleRx_DBA].[dbo].[PR_SWL_TEST]

@X = @X OUTPUT

Print @X

GO

2

10

10

Now, this came up because an SSIS package that uses a store procedure is now failing with this error "Cannot use the OUTPUT option when passing a constant to a stored procedure." We think it is passing constants like the first example that fails. The SP above was run from SSMS and not in an SSIS package.

Does anyone know why this behavior is happening? Is there a DB Option or an ansi setting that may have changed recently that should be changed back (although we don't know of any changes)? The production stored procedure has been work fine for months and failed with this error today.

I am running Microsoft SQL Server 2008 (SP2) standard - 10.0.4000.0 on Windows Server 2003 SP 2 Standard.

There were no recent changes to the operating environment.

thanks

Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 1

To my knowledge the SQL engine has always worked like that. I'm not sure if there have been any changes on the SSIS side that could result in a variable being replaced with the constant that it currently has. The best way to handle this may be to add a second variable that's just an output parameter. Have @x just be input so it can take a constant and have @xout just be an output so you never try to pass a constant to it.