Multiple if exists

(There is a reason I am separating the FreqID groups that is not yet evident in my code).

I want the code below to keep going to the next group whether it finds data in one or not. What is happening now is it stops looking once it finds one with data. What am I doing wrong?


CREATE PROCEDURE Prnt
@MemID AS INT
AS
if exists(Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=1)
BEGIN
Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=1
ORDER BY Food.GroupID, Food.HMID
END

if exists(Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=2)
BEGIN
Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=2
ORDER BY Food.GroupID, Food.HMID
END

if exists(Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=3)
BEGIN
Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=3
ORDER BY Food.GroupID, Food.HMID
END

if exists(Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=4)
BEGIN
Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=4
ORDER BY Food.GroupID, Food.HMID
END

if exists(Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=5)
BEGIN
Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=5
ORDER BY Food.GroupID, Food.HMID
END

if exists(Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=6)
BEGIN
Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=6
ORDER BY Food.GroupID, Food.HMID
END


if exists(Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=7)
BEGIN
Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=7
ORDER BY Food.GroupID, Food.HMID
END


if exists(Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=8)
BEGIN
Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=8
ORDER BY Food.GroupID, Food.HMID
END


if exists(Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=9)
BEGIN
Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=9
ORDER BY Food.GroupID, Food.HMID
END


if exists(Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=10)
BEGIN
Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=10
ORDER BY Food.GroupID, Food.HMID
END


if exists(Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=11)
BEGIN
Select Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID=11
ORDER BY Food.GroupID, Food.HMID
END
GO
LVL 1
net_susanAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>with the date for each group.
no problem, look at what you could do, I guess you find this good starting point:

CREATE PROCEDURE Prnt
@MemID AS INT
AS

SELECT Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID
, CASE
  WHEN Food.FreqID = 1 THEN CONVERT(varchar(10), StrtDate, 101)
  WHEN Food.FreqID = 2 THEN CONVERT(varchar(10), StrtDate, 102)
  WHEN Food.FreqID = 3 THEN CONVERT(varchar(10), StrtDate, 103)
  WHEN Food.FreqID = 4 THEN CONVERT(varchar(10), StrtDate, 104)

  WHEN Food.FreqID = 5 THEN CONVERT(varchar(10), dateadd(day, 1,StrtDate), 101)
  WHEN Food.FreqID = 6 THEN CONVERT(varchar(10), dateadd(week,1,StrtDate), 102)
  WHEN Food.FreqID = 7 THEN CONVERT(varchar(10), dateadd(month,-1,StrtDate), 103)
  WHEN Food.FreqID = 8 THEN CONVERT(varchar(10), dateadd(year, -2,StrtDate), 104)

  ... etc...
 
  END AS DATE_ONLY
, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID BETWEEN 1 AND 12
ORDER BY Food.FreqID, Food.GroupID, Food.HMID
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
It shouldn't stop unless it gets an error.  It may be that they are all being gen'd but you are only seeing one result set for some reason.  For example: certain commands only work on the first result set; you must scroll thru multiple result sets in ASP / other app code; etc..
0
 
DonKronosConnect With a Mentor Commented:
Try to cut and paste this code into Query Analyzer (without the create procedure statement) and see how many result sets you get.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I guess in query analyzer you get all the data you request, but not in the application code.
this is due to the fact that all the different SELECT's will return a separate recordset, which you have to catch apart.

in ADO, the recordset will be filled with the first select.
to get the next one(s), use the recordset's NextRecordset function which will return the next one.

0
 
net_susanAuthor Commented:
This is my ASP.NET code, any ideas would be helpful (although I realize I may need to open this question over there now). I've trimmed down my code a lot, but this is the basic idea.

                  Private Sub prntl ()

                Dim objconn As New SqlConnection("mystring")
               objconn.Open()
               try
               Dim MemID
            MemID = (Request.Cookies("sy")("CMID"))        
               Dim objcmd As New SqlCommand("mySP" , objconn)
               objCmd.CommandType = CommandType.StoredProcedure
Dim dr As SqlDataReader
               dr = objcmd.ExecuteReader()
               Dim currentGroup as string = ""

Dim dt as DataTable = new DataTable()
Dim GroupID, GroupName
dt.Columns.Add("GroupID")
dt.Columns.Add("HMNN")
dt.Columns.Add("HMID")
dt.Columns.Add("GroupName")
dt.Columns.Add("FoodID")
dt.Columns.Add("FoodDesc")
dt.Columns.Add("DATE_ONLY")
dt.Columns.Add("DayOne")
dt.Columns.Add("DayTwo")


Do While (dr.Read())
    Dim row as DataRow = dt.NewRow()
    row("GroupID")=dr("GroupID")
    row("GroupName")=dr("GroupName")
 row("HMNN")=dr("HMNN")
 row("HMID")=dr("HMID")
     row("FoodID")=dr("FoodID")
    row("FoodDesc")=dr("FoodDesc")
    row("Date_Only") = Convert.ToDateTime(dr("Date_Only").ToString()).ToShortDateString()
        row("DayOne")=dr("DayOne")
    row("DayTwo")=dr("DayTwo")
 
    dt.Rows.Add(row)
Loop

               dataList1.DataSource = dt
               dataList1.DataBind()
               dr.Close
             
               finally
               objConn.Close()
               objConn.Dispose()
               end try
               
               End Sub
0
 
net_susanAuthor Commented:
I actually wish instead of changing my ASP.NET code, I could somehow re-merge these into one recordset before the ASP.NET page reads it.
0
 
net_susanAuthor Commented:
I wish I could merge them into one and then do my ORDER BY command.
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
What about simplifying your procedure:

CREATE PROCEDURE Prnt
@MemID AS INT
AS

SELECT Food.FoodDesc, Food.FoodID, Food.GroupID, Food.FreqID, CONVERT(varchar(10), StrtDate, 101) AS DATE_ONLY, DayOne, DayTwo, Freq.HowOft, Food.HMID, HMs.HMNN, FoodGroups.GroupName
from Food
INNER JOIN Freq ON Food.FreqID = Freq.FreqID
INNER JOIN FoodGroups ON Food.GroupID = FoodGroups.GroupID
INNER JOIN HMs ON Food.HMID = HMs.HMID
WHERE Food.MemberID = @MemID
AND Food.FreqID BETWEEN 1 AND 12
ORDER BY Food.FreqID, Food.GroupID, Food.HMID

GO
0
 
net_susanAuthor Commented:
That's pretty much the way it started, but I'm going to need it to do different things with the date for each group.
0
 
net_susanAuthor Commented:
Great! There will be more related q's to come, I'll post the links here tomorrow.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.