July 5, 2011 at 5:40 pm
Hello!
My issue is that my stored procedure only works if there is data in the table that it's referencing. For example:
Table A:
----------------------------------
Type | Totals
----------------------------------
Active 0
Table B:
----------------------------------
TotalActive
------------------------------------
5
So basically if Table A's values are Null, that is there are no records with Type 'Active' in them, then it should show the Total Active column as 5, or do a subtraction 5-0(Null) = 5
Here's my stored procedure:
ALTER PROCEDURE [dbo].[GetDaysLeft]
@ID int
AS
DECLARE
@DaysLeft float,
@DaysTaken float,
@TotalDays float
SET @DaysLeft = 0
SET @DaysTaken = (SELECT SUM(DayType) from dbo.Tectonic WHERE
Type = 'Active' AND ID = @ID)
SET @TotalDays = (SELECT Totals from dbo.Users WHERE ID = @ID)
SET @Daysleft = @TotalDays - @DaysTaken
Select @DaysLeft AS FSDLeft
Just by itself, the stored procedure does the calculations properly, only when there is a record in Table A as 'Active', otherwise when there is no data then it doesn't return anything because there are no values in Table A stating 'Active'.
It either doesn't return anything or it returns only 5 (which is the total in Table B) which is partially right, but when I put in a record stating Active in Table A, it again returns 5 instead of 4. This happened after I tried:
(Select Coalesce(0, SUM(DayType)) from dbo.Tectonic Where Type = 'Active' AND ID = @ID)
Any ideas?
July 5, 2011 at 10:23 pm
use ISNULL function while doing the final substraction
@Daysleft = ISNULL(@TotalDays,0) - ISNULL(@DaysTaken,0)
July 6, 2011 at 7:45 am
The reason you're getting that problem is because, if you don't assign a value to a variable after declaring it, then it leaves the value of the variable as NULL. When you do a query in which you are assigning the results to a variable, if the query returns no results, the variable remains with the same value it had prior to the query. This is why it is important to assign values to variables after declaring them, unless you specifically want the NULL result. When doing any operation involving NULL values, the result is always NULL - IE if you say "5 - NULL", the output is going to be NULL.
Also, the usual convention for assigning variables to values from queries is to do it in the SELECT statement inself. This allows you to assign multiple variables to different values from the query.
In other words, change your procedure to this:
ALTER PROCEDURE [dbo].[GetDaysLeft]
@ID int
AS
DECLARE
@DaysLeft float,
@DaysTaken float,
@TotalDays float
SET @DaysLeft = 0
SET @DaysTaken = 0
SET @TotalDays = 0
SELECT @DaysTaken = SUM(DayType) from dbo.Tectonic WHERE Type = 'Active' AND ID = @ID
SELECT @TotalDays = Totals from dbo.Users WHERE ID = @ID
SET @Daysleft = @TotalDays - @DaysTaken
Select @DaysLeft AS FSDLeft
Though one thing you should note is that, if the second query, "SELECT @TotalDays = Totals from dbo.Users WHERE ID = @ID", returns more than one result, the value assigned to @TotalDays will be the last row returned.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply