August 12, 2015 at 7:12 pm
DECLARE @p1 INT
DECLARE @p2 INT
SET @p1 = CAST((SELECT (col1 + col2)
FROM #STOCK
WHERE DAYSTOEXPIRY = '0')AS INT)
SET @p2 = CAST((SELECT SUM(col1 + col2)
FROM #STOCK)AS INT)
IF ( @p2-@p1 < 4)
SET @message = 'Current Count = ' + CAST(@p2 AS NVARCHAR(100)) + '<br/>' + ' Midnight Count = ' + CAST(@p2-@p1 AS NVARCHAR(100)) + '<br/>'
---------------------------------------------------------------------------------------------------------
In the above code snippet if @p1 = 0 then the query crashes
Please let me know what am I doing wrong?
Thanks in advance,
SM
August 12, 2015 at 7:16 pm
What do you mean by "crash"? What's the exact error message you're receiving?
Cheers!
August 12, 2015 at 7:20 pm
later I am trying to send @message in email @body
and on receiving the email there are no contents
As such, there are no errors appearing on sql.
If @p1 != 0, the query runs as expected but when it is zero, the mail does not have anything in the body.
August 12, 2015 at 7:23 pm
In short I want to know:
How to cast integer value zero to string value '0'
Many thanks!
August 12, 2015 at 7:31 pm
That sounds more like @p1 is NULL. Have you confirmed that the value of @p1 is actually zero and not null in these cases?
I'd suggest adding the following SELECT after all the variables have had values assigned:
SELECT @p1, @p2, @message
It seems likely that @p1 is NULL in the situations you've described, not 0.
You don't have to do anything special to cast 0 as character data:
DECLARE @p1 int;
SET @p1=0;
SELECT 'This is a string representation of '+CAST(@p1 AS CHAR(1));
If it turns out that it is NULL in those situations, you could just use the ISNULL function to handle it as is appropriate for your purposes.
Cheers!
August 12, 2015 at 7:45 pm
I am getting value of @p1 as :
SET @p1 =(SELECT (col1+ col2)
FROM #STOCK
WHERE DAYSTOEXPIRY = '0')
Now, if @p1 is zero, there is nothing displayed in the message which is trying to print CAST(@p2-@p1 AS NVARCHAR(100))
Otherwise if @p1 != 0 then all is fine
August 12, 2015 at 8:01 pm
I know that's how you're assigning the variable a value. What I'm saying is that the behavior you're describing indicates the value is NULL, not 0.
I'm suggesting running a SELECT after the variable is assigned a value so you can see if it is in fact NULL or 0.
I know you're saying that the problem happens when you think the value is 0, but that needs to be confirmed. As the second bit of code showed, there is nothing special about casting 0 as character data, so that shouldn't be an issue. Concatenating a string with a NULL, on the other hand, would lead to exactly what you're seeing (technically, in this case you wouldn't be concatenating with a NULL, because if @p1 is NULL, then the assignment to @message in the IF block will never happen; the result will be the same, though).
We need to know for sure what the values of all those variables, and that's what SELECTing the variables after they're assigned is for. There are a few scenarios that could lead to @message being blank, and they depend on the value of the variables when the IF block is reached.
Once we know for sure the values of the variables when @message is blank, we can decide how to proceed.
Cheers!
August 12, 2015 at 8:05 pm
Many Thanks!
ISNULL helped.
I used:
SET @p1 = ISNULL((SELECT (col1+ col2)
FROM #STOCK
WHERE DAYSTOEXPIRY = '0'),0)
and it worked.
Regards,
SM
August 12, 2015 at 8:14 pm
I'm glad I could help 🙂
Cheers!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply