case satement in where for if number not null? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

case satement in where for if number not null?

Author

Topic

petebob796
Starting Member

35 Posts

Posted - 2008-03-17 : 05:46:13

I am trying to setup a query that receives input variables one being a number which could be null (could receive as string).

What I want to happen is if a number is sent the data is filtered by that number but if it is empty no filtering is performed I think this involves using a case statement in a where clause but can't work out the syntax I am using sql 200 server.

I think the case statement is something like this:
(week_no = CASE @week WHEN null THEN '#' ELSE @week END)
or maybe I am way off

I considered using like @inNumber + '%' but this would cause the problem of if I inoput 2 it would also return 20's 200's ...

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-17 : 06:19:50

Hi,

Case when week_no = '' Then '#' Else @Week End

petebob796
Starting Member

35 Posts

Posted - 2008-03-17 : 07:07:56

I think I tried that before but anyway I put it back in full sql is:

SELECT     student_id, acad_period, register_id, register_group, week_no, absence_code, attendance_type
FROM         dbo.sttdstud
WHERE     (student_id LIKE @stuID + '%') AND (register_id LIKE @regID + '%') AND (register_group LIKE @regGroup + '%') AND 
                     Case when week_no = '' Then '#' Else @Week End

got an unable to parse error. I should say this is in a c# table adapter query (incase it's important).

petebob796
Starting Member

35 Posts

Posted - 2008-03-17 : 07:16:47

just tried:

SELECT     student_id, acad_period, register_id, register_group, week_no, absence_code, attendance_type
FROM         dbo.sttdstud
WHERE     (student_id LIKE '%') AND (register_id LIKE '%') AND (register_group LIKE '%') AND (week_no = CASE @Week WHEN NULL THEN '#' ELSE @Week END)

this works through management studio query but doesn't in C# comes up with error this server doesnt support udp (user defined types). Guess it wasn't an sql problem.

Thanks for the help anyway

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources