Problem getting print to show variable 2

[tigerjade]

I have an SP that, among other things, needs to sum some values and play with it. It seems to be dying, because all I get is the two selected values at the top:

ALTER PROCEDURE sp_addItemsAccounting
@ItemID int
,@Received int
,@UserID int
,@InvoiceID int
,@Success int Output
AS

DECLARE @Quantity int, @CurrentSum int

SELECT CONVERT(int, Quantity) AS Quantity FROM Item WHERE ID = @ItemID
SELECT SUM(Received) AS CurrentSum FROM OutstandingItemsAccounting WHERE ItemID = @ItemID

IF (@Quantity >= (@CurrentSum + @Received))
BEGIN
	IF NOT EXISTS (SELECT ID FROM OutstandingItemsAccounting WHERE ItemID = @ItemID AND DATEDIFF(ss, DateVerified, GetDate()) < '10')
	BEGIN
		INSERT INTO OutstandingItemsAccounting (ItemID, Received, DateVerified, UserID, InvoiceID) 
		VALUES (@ItemID, @Received, GetDate(), @UserID, @InvoiceID)
		SET @Success = 1
	END
	ELSE
	BEGIN
		SET @Success = 0
	END
END
ELSE
BEGIN
	SET @Success = 0
END

GO

When I run this in QA, I see the CurrentSum and Quantity values, but nothing else. I can't get QA to print anything at all, so I can't find out what the combined value of the two is or if it's even entering the if/else clause.

What am I doing wrong?

thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[SQLDenis]

I don't see any print statements
Could they be NULL. try using COALESCE or ISNULL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Until Mircosoft comes with Mind-Reading Development Kit (tm), that won't fill @variables as you expect. Try:

SELECT [b]@Quantity =[/b] CONVERT(int, Quantity) FROM Item WHERE ID = @ItemID

Ditto for other two variables.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[tigerjade]

vongrunt, when I try what you suggest, I don't get a value returned at all. I had left some test code in that when I posted; here's what I'm trying currently:

ALTER PROCEDURE sp_addItemsAccounting
@ItemID int
,@Received int
,@UserID int
,@InvoiceID int
,@Success int Output
AS

DECLARE @Quantity int, @CurrentSum int

SELECT Quantity FROM Item WHERE ID = @ItemID
SELECT SUM(Received) AS CurrentSum FROM OutstandingItemsAccounting WHERE ItemID = @ItemID

PRINT 'some nonsense here'
IF (@Quantity >= (@CurrentSum + @Received))
BEGIN
    PRINT 'insert'
	IF NOT EXISTS (SELECT ID FROM OutstandingItemsAccounting WHERE ItemID = @ItemID AND DATEDIFF(ss, DateVerified, GetDate()) < '10')
	BEGIN
		INSERT INTO OutstandingItemsAccounting (ItemID, Received, DateVerified, UserID, InvoiceID) 
		VALUES (@ItemID, @Received, GetDate(), @UserID, @InvoiceID)
		SET @Success = 1
	END
	ELSE
	BEGIN
		SET @Success = 0
	END
END
ELSE
BEGIN
    PRINT 'forget it'
	SET @Success = 0
END

GO

There are no null values, 'cause it's returning a result for both CurrentSum and Quantity when I test in QA. However, that very next Print statement doesn't print, and neither do the Print statements in the If/Else clause. All I see in QA when I run the SP is:

Quantity
1      8

CurrentSum
1      7

Am I not using the Print statements correctly?


thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[vongrunt]

This:

SELECT Quantity FROM Item WHERE ID = @ItemID

... is not what I suggested.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

you are doing a select not print
SELECT Quantity FROM Item WHERE ID = @ItemID
instead of

declare @Quantity int
SELECT Quantity =Quantity FROM Item WHERE ID = @ItemID
print @Quantity for example

The print statements will be in the messages output window

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[tigerjade]

No, it's not. If I use

SELECT @Quantity = CONVERT(int, Quantity) FROM Item WHERE ID = @ItemID

I don't get a return at all. That's why I figured it wasn't working. Am I wrong? Is it working if it DOESN'T show up in the QA window?

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[tigerjade]

That WAS it, vongrunt! I was mistaking output with variable assignment, apparently. Guess that's what happens when you learn these things on the fly. Thanks, guys!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[SQLDenis]

you want output or print?

SELECT @Quantity = CONVERT(int, Quantity) FROM Item WHERE ID = @ItemID

Print convert(varchar,@Quantity)

select @Quantity

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/