CODEWITHSUNDEEP
sqlsql-server
Greg
Greg

Reputation: 1710

Output Parameter Value is Not Returned

The store procedure does work fine and it print the values while i'm testing. But once i want to use the output in the execute command. it returns the prints from the store procedure and 2 blank lines from the execute. i keep on reading the code but it looks fine to me. Can someone look at this. My store procedure goes as this:

if OBJECT_ID ('dbo.usp_OmzetPerFilm')is not null
drop procedure usp_OmzetPerFilm
go 


create procedure usp_OmzetPerFilm
@film varchar(250),
@jaar int,
@jaaromzet float output ,
@TotaalBezoekers int output
as
begin

Select @TotaalBezoekers  = Sum(AantalTickets  ) from ticketVerkoop 
 Inner Join prijsTicket  on prijsTicket .idPrijs  = ticketVerkoop .idPrijs 
 Inner Join film  on film.idfilm   = ticketVerkoop .idfilm 
where @jaar = Jaar and @film = film.naam 

Select @jaarOmzet  = Sum(prijsTicket .prijs  * ticketVerkoop.AantalTickets  ) from ticketVerkoop 
 Inner Join prijsTicket  on prijsTicket .idPrijs  = ticketVerkoop .idPrijs 
  Inner Join film  on film.idfilm   = ticketVerkoop .idfilm 
 where @jaar = Jaar and @film = film.naam 


 --This does work
 print @jaaromzet
 print @totaalBezoekers

end 
go

But when i use the execute task. It only prints the values form the store procedure but not from the execute command. Is there something i'm missing ?

Declare @TotaleOmzet int
Declare @TotaleBezoekers int
EXECUTE usp_OmzetPerFilm 'hobbit',2014,@TotaleOmzet, @TotaleBezoekers

-- This does not work ?
print @TotaleOmzet
print @TotaleBezoekers

Upvotes: 1

Views: 64

Answers (2)

Jeroen Mostert
Jeroen Mostert

Reputation: 28769

You need to explicitly specify that the parameter is to be used for OUTPUT:

DECLARE @TotaleOmzet FLOAT
DECLARE @TotaleBezoekers INT 
EXECUTE usp_OmzetPerFilm 
    @film = 'hobbit',
    @jaar = 2014,
    @jaaromzet = @TotaleOmzet OUTPUT, 
    @TotaalBezoekers =  @TotaleBezoekers OUTPUT

Also, review your types -- you almost certainly want to use DECIMAL for financial totals, not FLOAT, which can run into accuracy issues.

Upvotes: 1

CiucaS
CiucaS

Reputation: 2128

Declare @TotaleOmzet int
Declare @TotaleBezoekers int
EXECUTE usp_OmzetPerFilm 'hobbit',2014,@TotaleOmzet OUTPUT, @TotaleBezoekers OUTPUT

-- This does not work ?
print @TotaleOmzet
print @TotaleBezoekers

You forgot the OUTPUT word when executing the procedure.

Upvotes: 2

Related Questions