Home IF EXISTS in Setting a variable in SQL
Reply: 1

IF EXISTS in Setting a variable in SQL

MadelineK
1#
MadelineK Published in 2017-09-13 03:20:18Z

How to have an if exists clause in setting a variable?

I have this set up so the results of the top select statement (@F1Runs, @F2Runs, @F3Runs, all for a corresponding Date) insert into the temp table #WeekEnding, then from there into an actual table.

The problem is that for some dates there is no 'Number of Runs' results for some or all of the furnaces. So, I am trying to find a way to put an IF EXISTS statement or something like that when I am setting the variables @F1Runs, @F2Runs, and @F3Runs, so that they insert into the table as 0 when they do not exist.

The error I get is:

Cannot insert the value NULL into column 'F2Runs', table 'WWALMDB.dbo.WeeklyRuns'; column does not allow nulls. INSERT fails.

My code:

Select 
    jr.FurnaceID, Count(Distinct jr.JobID) As 'Number of Runs'
Into 
    #WeekEnding
From 
    dbo.JobReports jr
Where 
    jr.StartDateTime >= @StartDate 
    and jr.EndDateTime < @Enddate
Group By  
    jr.FurnaceID
Order By 
    Count(jr.JobID) DESC

Select @F1Runs = [Number of Runs]
From #WeekEnding
Where FurnaceID = 1

Select @F2Runs = [Number of Runs] 
From #WeekEnding
Where FurnaceID = 2

Select @F3Runs = [Number of Runs] 
From #WeekEnding
Where FurnaceID = 3

If Exists (Select wr.WeekEnding
           From WWALMDB.dbo.WeeklyRuns wr
           Where wr.WeekEnding = DATEADD(day, -1, @Enddate))
Begin
    Update WWALMDB.dbo.WeeklyRuns 
    Set F1Runs = @F1Runs,
        F2Runs = @F2Runs, 
        F3Runs = @F3Runs
    Where WeeklyRuns.WeekEnding = DATEADD(day, -1, @Enddate)
End
Else
Begin
    Insert Into WWALMDB.dbo.WeeklyRuns (WeekEnding, F1Runs, F2Runs, F3Runs)
    Values (DATEADD(day, -1, @Enddate), @F1Runs, @F2Runs, @F3Runs)
End
Mr Slim
2#
Mr Slim Reply to 2017-09-13 03:30:07Z

It is my understanding that IF Exists is going to require a block, so i don;t believe this will help you.

I think ISNULL is your friend here

Select jr.FurnaceID, Count(Distinct jr.JobID) As 'Number of Runs'
Into #WeekEnding
From dbo.JobReports jr
Where jr.StartDateTime >= @StartDate and jr.EndDateTime < @Enddate
Group By jr.FurnaceID
Order By Count(jr.JobID) DESC

SET @F1Runs = ISNULL(Select [Number of Runs]
From #WeekEnding
Where FurnaceID = 1,0)

SET @F2Runs = (Select [Number of Runs] 
From #WeekEnding
Where FurnaceID = 2,0)

SET @F3Runs  = (Select [Number of Runs] 
From #WeekEnding
Where FurnaceID = 3,0)

If Exists ( Select wr.WeekEnding
            From WWALMDB.dbo.WeeklyRuns wr
            Where wr.WeekEnding = DATEADD(day, -1, @Enddate))
Begin
    Update WWALMDB.dbo.WeeklyRuns 
    Set F1Runs = @F1Runs,
        F2Runs = @F2Runs, 
        F3Runs = @F3Runs
    Where WeeklyRuns.WeekEnding = DATEADD(day, -1, @Enddate)
    End
    Else
Begin
    Insert Into WWALMDB.dbo.WeeklyRuns (WeekEnding, F1Runs, F2Runs, F3Runs)
    Values (DATEADD(day, -1, @Enddate), @F1Runs, @F2Runs, @F3Runs)
End

OR

Select jr.FurnaceID, Count(Distinct jr.JobID) As 'Number of Runs'
Into #WeekEnding
From dbo.JobReports jr
Where jr.StartDateTime >= @StartDate and jr.EndDateTime < @Enddate
Group By jr.FurnaceID
Order By Count(jr.JobID) DESC

Select @F1Runs = [Number of Runs]
From #WeekEnding
Where FurnaceID = 1

Select @F2Runs =[Number of Runs] 
From #WeekEnding
Where FurnaceID = 2

Select @F3Runs = [Number of Runs] 
From #WeekEnding
Where FurnaceID = 3

If Exists ( Select wr.WeekEnding
            From WWALMDB.dbo.WeeklyRuns wr
            Where wr.WeekEnding = DATEADD(day, -1, @Enddate))
Begin
    Update WWALMDB.dbo.WeeklyRuns 
    Set F1Runs = ISNULL(@F1Runs,0),
        F2Runs = ISNULL(@F2Runs,0), 
        F3Runs = ISNULL(@F3Runs,0)
    Where WeeklyRuns.WeekEnding = DATEADD(day, -1, @Enddate)
    End
    Else
Begin
    Insert Into WWALMDB.dbo.WeeklyRuns (WeekEnding, F1Runs, F2Runs, F3Runs)
    Values (DATEADD(day, -1, @Enddate), @F1Runs, @F2Runs, @F3Runs)
End
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.355737 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO