A problem getting value out of Stored Procedure

Server management studio does not give error from following query, but output parameter (kokonaissumma) is always NULL. I tested it other ways, by making it return value then it worked. But that required changes to the query, so I really don't know. The problem query is the last.

create PROCEDURE [dbo].[kori2]
(
@.Tuotekoodi varchar(20),
@.kokonaissumma money output
)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_osto WHERE Tuotekoodi=@.Tuotekoodi)
BEGIN
INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)
SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv
FROM dbo.t_Tuote
WHERE Tuotekoodi= @.Tuotekoodi
END
ELSE
BEGIN
UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
END
return (SELECT count(*) FROM dbo.t_osto)
select @.kokonaissumma =sum(Yhteensa)FROM dbo.t_osto

sum(Yhteensa)FROM dbo.t_osto

your missing a space:

sum(Yhteensa) FROM dbo.t_osto

|||

Move your SELECTs before the RETURN.

create PROCEDURE [dbo].[kori2]( @.Tuotekoodivarchar(20),@.kokonaissummamoney output)ASBEGIN SET NOCOUNT ON;IFNOT EXISTS(SELECT *FROM dbo.t_ostoWHERE Tuotekoodi=@.Tuotekoodi)BEGIN INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,AlvFROM dbo.t_TuoteWHERE Tuotekoodi= @.TuotekoodiENDELSE BEGIN UPDATE dbo.t_ostoSET Maara=Maara+1WHERE Tuotekoodi=@.TuotekoodiENDselect @.kokonaissumma =sum(Yhteensa)FROM dbo.t_ostoEND

|||

It works. Thank you both. It looks like this now.

...

...

UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
return (SELECT count(*) FROM dbo.t_osto)
END

Regards

Leif

|||

You dont need the return statement. The count is being returned through the OUTPUT parameter.

|||

I see. I'll fix that too. Below is part of the query now, it has now all features what I planned.

--

--

select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto --total money
select @.tuotemaara =sum(Maara) FROM dbo.t_osto -- how many items

return (SELECT count(*) FROM dbo.t_osto) --how many lines (and no return)

END

I should learn some sql. It is my weakest point in ASP.NET. I looked at "Books on line", but even first page used so unfamiliar terms, I was not able to go much further than that. Well, search is there and today I found some tutorials in there.

Thanks

Leif

|||

Hi again

I tried my stored procedure without return. Like this. This is my latest a refresh only version.

create PROCEDURE [dbo].[kori3paivitys]
(
@.kokonaissumma money output,
@.tuotemaara numeric(18, 0) output
)
AS
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
select @.tuotemaara =sum(Maara) FROM dbo.t_osto
SELECT count(*) FROM dbo.t_osto

With this the return value was 0. That was not the correct value. I wonder what else is wrong in my code.

This gave correct value as before. Below.

create PROCEDURE [dbo].[kori3paivitys]
(
@.kokonaissumma money output,
@.tuotemaara numeric(18, 0) output
)
AS
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
select @.tuotemaara =sum(Maara) FROM dbo.t_osto
return (SELECT count(*) FROM dbo.t_osto)

Regards

Leif