Help required Manipulating SQL Using CASE or IF

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 2005 Forums

Transact-SQL (2005)

Help required Manipulating SQL Using CASE or IF

Author

Topic

Firebrand
Starting Member

24 Posts

Posted - 2010-07-07 : 05:01:06

Hello everyone,

I moved on to this question yesterday from another post, but didn't explain myself very well, so here it is again (Thanks again to everyone who tried to help).

This is a simplified version of a procedure I'm working on. I'm passing four integer variables across to the SP, including @CourseID.

I'm using @CourseID as part of a filter in the WHERE clause.

Occasionly @CourseID won't exist in my web page so I'll be passing @CourseID as 00. When this occurs, I'd like the filter to read 'AND c.CourseID IS NOT NULL'

I've tried a CASE and IF statement with no success. I'm thinking that maybe I have to define the complete AND filter line earlier on as part of an argument and then insert it into the SELECT Statement when required.

Any help would be appreciated, and thanks again for your patience.

CREATE PROCEDURE [dbo].[fbt_course_call_action]

@CourseID int,
@CountryID int,
@LanguageID int,
@ContentType int

AS

SELECT *
FROM tblContent AS c
WHERE c.CountryID = @CountryID
AND c.LangaugeID = @LanguageID
-- c.CourseID filter here
AND c.CourseID = @CourseID
AND c.ContentType = @ContentType

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-07 : 05:07:06

[code]SELECT *
FROM ...
WHERE ...
AND (c.CourseID IS NOT NULL OR c.CourseID = @CourseID)[/code]
This will work because
1) When @CourseID has a value all NULLs are automatically removed anyway
2) When @CourseID is NULL, the "IS NOT NULL" have presedence.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-07 : 05:08:14

And, of course, if you don't know the value of @CourseID, don't push it as "00" and mix logic into a variable.

N 56°04'39.26"
E 12°55'05.63"

Firebrand
Starting Member

24 Posts

Posted - 2010-07-07 : 05:23:22

Thanks very much for your help.

I am passing @CourseID from my web page, if I don't have some kind of value I'll get an error, hence passing 00. I can't pass an empty variable.

Thanks again.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-07 : 17:40:15

quote:
Originally posted by Peso

SELECT *
FROM ...
WHERE ...
AND (c.CourseID IS NOT NULL OR c.CourseID = @CourseID)

This will work because
1) When @CourseID has a value all NULLs are automatically removed anyway
2) When @CourseID is NULL, the "IS NOT NULL" have presedence.

N 56°04'39.26"
E 12°55'05.63"

Does that satisfy the requirement? I think I might be missing something, but that'll return the same data set no matter what the value of @CourseID. Is, that the goal?

Here are some samples:

DECLARE @tblContent TABLE (CourseID INT)

INSERT @tblContent
SELECT 1
UNION ALL SELECT 5
UNION ALL SELECT NULL
UNION ALL SELECT 3
UNION ALL SELECT 2
UNION ALL SELECT NULL
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9

DECLARE @CourseID INT

--SET @CourseID = NULL
SET @CourseID = 3

-- Logic 1 (Peso's)
SELECT *
FROM @tblContent AS C
WHERE c.CourseID IS NOT NULL OR c.CourseID = @CourseID

-- Logic 2
SELECT *
FROM @tblContent AS C
WHERE c.CourseID = @CourseID OR (@CourseID IS NULL AND c.CourseID IS NOT NULL)

Firebrand, Can you confirm the results? If they are not correct, can you supply some sample data and expected output?
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources