print @variable probem

[I got a serious problem on print @.variable, please be kind to take a look]
CREATE PROCEDURE dbo.Get_seainvno
@.prefix varchar(20),
@.countno int,
@.period varchar(6),
@.result int output
AS
DECLARE @.testing int
IF @.countno > 0
BEGIN
SELECT number AS result
FROM arinvNo
WHERE prefix = @.prefix
print @.result <--I can show
SELECT @.testing = @.result %100
print @.testing <----It
can't show the variable in SQL analayzer ? Pls help
IF (@.result % 100) >= 90
BEGIN
UPDATE arinvNo
SET number = number + 75
WHERE prefix = @.prefix
END
ELSE
BEGIN
UPDATE arinvNo
SET number = number + 1
WHERE prefix = @.prefix
ENDDid you put out the length of the result, to see wheter it exsists ?
Additionaly it would put some identifier around this:
print 'The result after is:' + Cast(ISNULL(@.result,'Value is NULL') as
varchar(20))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Ray" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23wiSwouSFHA.3152@.TK2MSFTNGP12.phx.gbl...
> [I got a serious problem on print @.variable, please be kind to take a
> look]
> CREATE PROCEDURE dbo.Get_seainvno
> @.prefix varchar(20),
> @.countno int,
> @.period varchar(6),
> @.result int output
> AS
> DECLARE @.testing int
> IF @.countno > 0
> BEGIN
> SELECT number AS result
> FROM arinvNo
> WHERE prefix = @.prefix
> print @.result <--I can show
> SELECT @.testing = @.result %100
> print @.testing
> <----It can't show
> the variable in SQL analayzer ? Pls help
> IF (@.result % 100) >= 90
> BEGIN
> UPDATE arinvNo
> SET number = number + 75
> WHERE prefix = @.prefix
> END
> ELSE
> BEGIN
> UPDATE arinvNo
> SET number = number + 1
> WHERE prefix = @.prefix
> END
>|||Ray
I try and always use a SET when settting variables. The reason for this is
when the select returns a null you won't get unexpected or null results. I
know you can use isnull or set concat_null_yields_null but it's a lot of
work around for something that could have been prevented in the first place.
Coming from a C background I also like to always initialise variables. Even
if it's not needed.
Instead of select @.var = col from table
I would use
Set @.var = 0
I would use set @.var = (select col from table)
But to come back to your problem without changing your code... what's the
parameters your running this procedure with. I'm sure you are no noddy but
you sure you want to use % modulus , when you modulus something is will
always return everything n - 1 when mod n thus 99 - 91 will fall through you
r
loop. Is this suppose to happen ?
"Ray" wrote:

> [I got a serious problem on print @.variable, please be kind to take a look
]
> CREATE PROCEDURE dbo.Get_seainvno
> @.prefix varchar(20),
> @.countno int,
> @.period varchar(6),
> @.result int output
> AS
> DECLARE @.testing int
> IF @.countno > 0
> BEGIN
> SELECT number AS result
> FROM arinvNo
> WHERE prefix = @.prefix
> print @.result <--I can show
> SELECT @.testing = @.result %100
> print @.testing <----
It
> can't show the variable in SQL analayzer ? Pls help
> IF (@.result % 100) >= 90
> BEGIN
> UPDATE arinvNo
> SET number = number + 75
> WHERE prefix = @.prefix
> END
> ELSE
> BEGIN
> UPDATE arinvNo
> SET number = number + 1
> WHERE prefix = @.prefix
> END
>
>|||Ray
I try and always use a SET when settting variables. The reason for this is
when the select returns a null you won't get unexpected or null results. I
know you can use isnull or set concat_null_yields_null but it's a lot of
work around for something that could have been prevented in the first place.
Coming from a C background I also like to always initialise variables. Even
if it's not needed.
Instead of select @.var = col from table
I would use
Set @.var = 0
I would use set @.var = (select col from table)
But to come back to your problem without changing your code... what's the
parameters your running this procedure with. I'm sure you are no noddy but
you sure you want to use % modulus , when you modulus something is will
always return everything n - 1 when mod n thus 99 - 91 will fall through you
r
loop. Is this suppose to happen ?
"Ray" wrote:

> [I got a serious problem on print @.variable, please be kind to take a look
]
> CREATE PROCEDURE dbo.Get_seainvno
> @.prefix varchar(20),
> @.countno int,
> @.period varchar(6),
> @.result int output
> AS
> DECLARE @.testing int
> IF @.countno > 0
> BEGIN
> SELECT number AS result
> FROM arinvNo
> WHERE prefix = @.prefix
> print @.result <--I can show
> SELECT @.testing = @.result %100
> print @.testing <----
It
> can't show the variable in SQL analayzer ? Pls help
> IF (@.result % 100) >= 90
> BEGIN
> UPDATE arinvNo
> SET number = number + 75
> WHERE prefix = @.prefix
> END
> ELSE
> BEGIN
> UPDATE arinvNo
> SET number = number + 1
> WHERE prefix = @.prefix
> END
>
>|||On Wed, 27 Apr 2005 13:30:19 +0800, Ray wrote:

>[I got a serious problem on print @.variable, please be kind to take a look]
(snip)
> SELECT number AS result
> FROM arinvNo
> WHERE prefix = @.prefix
>print @.result <--I can show
(snip)
Hi Ray,
I'm not sure why this PRINT shows anything, nor what it shows - but it
won't be the number just retrieved from the arinvNo table.
I think you want this syntax:
SELECT @.result = number
FROM arinvNo
WHERE prefix = @.prefix
(Note the changed SELECT clause - I added the @. to indicate a variable and
changed the syntax to make it an assignment SELECT statement).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)