Case in WHERE clause problem? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Case in WHERE clause problem?

Author

Topic

DTFan
Yak Posting Veteran

52 Posts

Posted - 2006-01-16 : 10:02:24

I've searched the boards and have tried many different approaches but I can't seem to get this to work. Basically I'm trying to write a stored procedure that accepts 5 parameters (@in_intStudID, @in_intClubID, @in-intSQLType, @in_dtDateFrom, @in_dtDateTo). If the @in_intSQLType = 1 Then I'm doing a search looking for a specific date. If it's equal to 3 then I'm doing a search looking for records Between 2 dates.


     SELECT   s.studID, cm.clubID, s.studLName + ', ' + s.studFName AS studFullName, s.studEmail
     FROM     tblClubMembership cm INNER JOIN tblStudent s ON cm.studID = s.studID
     WHERE    s.studID = @in_intStudID
     AND      cm.clubID = @in_intClubID
     AND
          CASE @in_intSQLType
               WHEN 1 THEN [CONVERT(DATETIME, CONVERT(VARCHAR(11), cm.joinDate, 101)) = @in_dtDateFrom]
               ELSE [CONVERT(DATETIME, CONVERT(VARCHAR(11), cm.joinDate, 101)) BETWEEN @in_dtDateFrom AND @in_dtDateTo]
          END

I initially tried doing it without using @in_intSQLType, using only @in_dtDateTo = NULL, but I wasn't able to get that working.

Can anyone tell me what I'm doing wrong? Should I just use an If-Else here, or is the Case the better way to go? Or should I stick with the @in_dtDateTo = NULL method?

Thanks in advance for any and all help that you can give.

DTFan
Ever-hopeful programmer-in-training

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-01-16 : 10:11:44

quote:
I initially tried doing it without using @in_intSQLType, using only @in_dtDateTo = NULL, but I wasn't able to get that working.

This is a solution for that method:

Select blah blah
Where s.studID = @in_intStudID
AND cm.clubID = @in_intClubID
AND cm.joinDate >= @in_dtDateFrom
AND (@in_dtDateTo is null or cm.joinDate <= @in_dtDateTo)

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources