OUTPUT Info needed

Question

OUTPUT Info needed

Denis Wilkinson

Mr or Mrs. 500

Points: 569
More actions
July 19, 2004 at 1:21 am

#161185

Hi,
I need to run a stored procedure with an OUTPUT, I use the following code:
CREATE Procedure dbo.WareTATCalcsShift
@StartDate varchar(22), @EndDate varchar(22), @shift varchar(1), @BTB int OUTPUT AS
set nocount on
DECLARE.....etc.
When I run the code in Query Analyser I get the following error:
Server: Msg 201, Level 16, State 4, Procedure WareTATCalcsShift, Line 0
Procedure 'WareTATCalcsShift' expects parameter '@BTB', which was not supplied.
Can someone tell me why the OUTPUT parameter is not recognised as such and it is being interpreted as an input?
regards
denis

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Since you haven't posted the whole code and your call, one can only guess. Take a look at BOL at OUTPUT parameters -> Returning Data Using OUTPUT Parameters. I suspect the problem being your way to call the procedure. If not, you should post your code to reproduce it.

HTH

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

July 19, 2004 at 5:03 am

#515048

how are you calling the procedure ?

MVDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I'm guessing you're only passing 3 parameters to the stored procedure

You have to declare and pass a variable for the proc to return a value. Output parameters are different to return codes.

Your call should look something like this

DECLARE @BTB int -- will contain the returned value

EXEC WareTATCalcsShift '2004-07-01', '2004-07-19', 'A', @BTB

Select @BTB AS Returned_Value

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Denis Wilkinson Mr or Mrs. 500 Points: 569 More actions · Answer 4

Hi,

That was it, I did not realise I had to declare the @BTB BEFORE the execute of the stored procedure. All working well now..thanks.