returns null not cost value in SP

I do not understand what I am doing wrong. I have no problem getting the
value into @.tot but I cannot get the value assigned to @.item_cost. When I
use the debug mode, it says @.item_cost is null before the start of the debug
and after the debugging is done.
CREATE PROCEDURE test
@.id int,
@.item_cost money = NULL out
AS
declare @.tot money
select @.tot = convert(money,(field_value))
from tbl
where field_id = @.id
and field_code = 901
set @.item_cost = @.tot
GO
Grant
Who gives a {censored} if I am wrong.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Why have the intermediate variable @.tot? Why not just assign the value
in the SELECT statement to the variable @.item_cost. And, why set a
default of NULL on the OUTPUT variable when, if the SELECT command
doesn't pick up anything, it will be NULL anyway?
CREATE PROCEDURE test
@.id int,
@.item_cost money out
AS
select @.item_cost = convert(money, field_value)
from tbl
where field_id = @.id
and field_code = 901
GO
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRFJOv4echKqOuFEgEQJPPwCfcFbZUZ5LjwCf
r6w/ssa5DdXaul8Ani4G
M3i/xn7vb7/mwkxaXsdhqe9B
=nln8
--END PGP SIGNATURE--
Grant wrote:
> I do not understand what I am doing wrong. I have no problem getting the
> value into @.tot but I cannot get the value assigned to @.item_cost. When I
> use the debug mode, it says @.item_cost is null before the start of the deb
ug
> and after the debugging is done.
>
>
> CREATE PROCEDURE test
> @.id int,
> @.item_cost money = NULL out
> AS
>
> declare @.tot money
>
> select @.tot = convert(money,(field_value))
> from tbl
> where field_id = @.id
> and field_code = 901
>
> set @.item_cost = @.tot
> GO
>|||I was struggling on getting the values returned back to me so I added
intermediate variable and I found that I was getting the expected result but
only to @.tot not @.item_cost. I made the changes to the query to match your
example and it still returns null instead of 20.00.
Grant
Who gives a {censored} if I am wrong.
"MGFoster" <me@.privacy.com> wrote in message
news:a9s4g.5073$An2.3038@.newsread2.news.pas.earthlink.net...
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Why have the intermediate variable @.tot? Why not just assign the value
> in the SELECT statement to the variable @.item_cost. And, why set a
> default of NULL on the OUTPUT variable when, if the SELECT command
> doesn't pick up anything, it will be NULL anyway?
> CREATE PROCEDURE test
> @.id int,
> @.item_cost money out
> AS
> select @.item_cost = convert(money, field_value)
> from tbl
> where field_id = @.id
> and field_code = 901
> GO
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBRFJOv4echKqOuFEgEQJPPwCfcFbZUZ5LjwCf
r6w/ssa5DdXaul8Ani4G
> M3i/xn7vb7/mwkxaXsdhqe9B
> =nln8
> --END PGP SIGNATURE--
> Grant wrote:|||Very confuse now. I got it to work but I have to print it, why? Changes are
in ucase.
CREATE PROCEDURE test
@.id int,
@.item_cost money = NULL out
AS
declare @.tot money
select @.tot = convert(money,(field_value))
from tbl
where field_id = @.id
and field_code = 901
set @.item_cost = @.tot
PRINT CONVERT(VARCHAR, @.item_cost))
GO
Grant
Who gives a {censored} if I am wrong.
"Grant" <email@.nowhere.com> wrote in message
news:%23FEYkSuaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>I do not understand what I am doing wrong. I have no problem getting the
>value into @.tot but I cannot get the value assigned to @.item_cost. When I
>use the debug mode, it says @.item_cost is null before the start of the
>debug and after the debugging is done.
>
>
> CREATE PROCEDURE test
> @.id int,
> @.item_cost money = NULL out
> AS
>
> declare @.tot money
>
> select @.tot = convert(money,(field_value))
> from tbl
> where field_id = @.id
> and field_code = 901
>
> set @.item_cost = @.tot
> GO
>
> --
> Grant
> Who gives a {censored} if I am wrong.
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Can you execute the SP from Query Analyzer? E.g.:
declare @.output money
exec test 255, @.item_cost = @.output OUTPUT
print @.output
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA+AwUBRFJnxoechKqOuFEgEQI+tACgljJ1DN4D
4IfLakOmWRWehBNUQBMAl2eH
YynUqeXDsI6xI87T63C9SbM=
=lDfp
--END PGP SIGNATURE--
Grant wrote:
> Very confuse now. I got it to work but I have to print it, why? Changes ar
e
> in ucase.
>
> CREATE PROCEDURE test
> @.id int,
> @.item_cost money = NULL out
> AS
> declare @.tot money
> select @.tot = convert(money,(field_value))
> from tbl
> where field_id = @.id
> and field_code = 901
> set @.item_cost = @.tot
> PRINT CONVERT(VARCHAR, @.item_cost))|||On Fri, 28 Apr 2006 14:00:37 -0400, Grant wrote:

>I was struggling on getting the values returned back to me so I added
>intermediate variable and I found that I was getting the expected result bu
t
>only to @.tot not @.item_cost. I made the changes to the query to match your
>example and it still returns null instead of 20.00.
Hi Grant,
How do you call the procedure?
EXEC test @.id = 1,
@.item_cost = @.result_variable
is WRONG!! (And I expect that this is how you currently do it).
The correct call is
EXEC test @.id = 1,
@.item_cost = @.result_variable OUTPUT
Note the addition of OUTPUT to specify that @.item_cost is an output
variable. You have to specify this both in the CREATE PROCEDURE and in
the EXECUTE statement in order to make it work.
Hugo Kornelis, SQL Server MVP