?
Solved

Multiple if exists

Posted on 2006-05-08
11
Medium Priority
?
359 Views
Last Modified: 2008-03-10
(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
0
Comment
Question by:net_susan
11 Comments
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 200 total points
ID: 16633871
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
 
LVL 15

Assisted Solution

by:DonKronos
DonKronos earned 200 total points
ID: 16633985
Try to cut and paste this code into Query Analyzer (without the create procedure statement) and see how many result sets you get.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 16634073
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 1

Author Comment

by:net_susan
ID: 16634300
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
 
LVL 1

Author Comment

by:net_susan
ID: 16634310
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
 
LVL 1

Author Comment

by:net_susan
ID: 16634327
I wish I could merge them into one and then do my ORDER BY command.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 16634359
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
 
LVL 1

Author Comment

by:net_susan
ID: 16634421
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 16634444
>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
 
LVL 1

Author Comment

by:net_susan
ID: 16634475
Great! There will be more related q's to come, I'll post the links here tomorrow.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question