CASE with empty parameter - Microsoft SQL Server

Not Me

Hi, I can't seem to get this where clause to function as I wish.. must be
something simple but I can't see it!

have tried
ac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course + '%'
ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course + '%'
ac.coursename like CASE len(@course) WHEN 0 THEN '%' ELSE '%' + @course +
'%'

All work fine when I enter something in the parameter, but not when I leave
it blank! Any ideas why this is the ...case?

Cheers!
Chris

Jul 20 '05 #1

Subscribe Reply

2173

Not Me

"Not Me" <No****@faker.fake.fa.kee> wrote in message
news:c9**********@ucsnew1.ncl.ac.uk...

Hi, I can't seem to get this where clause to function as I wish.. must be
something simple but I can't see it!

have tried
ac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course + '%' ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course + '%'
ac.coursename like CASE len(@course) WHEN 0 THEN '%' ELSE '%' + @course +
'%'

All work fine when I enter something in the parameter, but not when I leave it blank! Any ideas why this is the ...case?

Ok try #4, I did
ac.coursename like CASE WHEN @course is null THEN '%' ELSE '%' + @course +
'%'

And it works! Don't you just hate discovering the answer yourself straight
after asking the question? :op

Chris

Jul 20 '05 #2

Not Me

"Not Me" <No****@faker.fake.fa.kee> wrote in message
news:c9**********@ucsnew1.ncl.ac.uk...

"Not Me" <No****@faker.fake.fa.kee> wrote in message
news:c9**********@ucsnew1.ncl.ac.uk...

New problem :)

Along the same lines as above, i.e. wanting to include all rows if no
criteria specified - What can I do in the case of integers? what would be
the %/catch-all for this?
Is there a better way alltogether of using 'optional' parameters?

Any help much appreciated!
Chris

Jul 20 '05 #3

tperovic

I've been using this:

ac.coursename = COALESCE(@course,ac.coursename)

When @course is null then the column is compared to itself which is always
true.
"Not Me" <No****@faker.fake.fa.kee> wrote in message
news:c9**********@ucsnew1.ncl.ac.uk...

Hi, I can't seem to get this where clause to function as I wish.. must be
something simple but I can't see it!

have tried
ac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course + '%' ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course + '%'
ac.coursename like CASE len(@course) WHEN 0 THEN '%' ELSE '%' + @course +
'%'

All work fine when I enter something in the parameter, but not when I leave it blank! Any ideas why this is the ...case?

Cheers!
Chris

Jul 20 '05 #4

Not Me

"tperovic" <to*********@yahoo.com> wrote in message
news:pn******************@newsread2.news.atl.earth link.net...

"Not Me" <No****@faker.fake.fa.kee> wrote in message
news:c9**********@ucsnew1.ncl.ac.uk...
ac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course + '%'
ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course + '%' ac.coursename like CASE len(@course) WHEN 0 THEN '%' ELSE '%' + @course + '%'

All work fine when I enter something in the parameter, but not when I

leave it blank!

I've been using this:

ac.coursename = COALESCE(@course,ac.coursename)

When @course is null then the column is compared to itself which is always
true.

Aha, so much better! Thanks for that.

Chris

Jul 20 '05 #5

Erland Sommarskog

Not Me (No****@faker.fake.fa.kee) writes:

Along the same lines as above, i.e. wanting to include all rows if no
criteria specified - What can I do in the case of integers? what would be
the %/catch-all for this?
Depends on your business domain, but NULL would certainly be the
best choice.
Is there a better way alltogether of using 'optional' parameters?

Don't really know what you are into, but
http://www.sommarskog.se/dyn-search.html might be something for you.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #6

Not Me

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...

Not Me (No****@faker.fake.fa.kee) writes:
Along the same lines as above, i.e. wanting to include all rows if no
criteria specified - What can I do in the case of integers? what would be the %/catch-all for this?

Depends on your business domain, but NULL would certainly be the
best choice.
Is there a better way alltogether of using 'optional' parameters?

Don't really know what you are into, but
http://www.sommarskog.se/dyn-search.html might be something for you.

Thanks for that!

Chris

Jul 20 '05 #7

Similar topics

14597

SELECT CASE in ASP???

by: Newbie | last post by:

Dear friends, I am having a hard time understanding how to use a SELECT CASE in ASP. I have used it in VB but never in ASP scripting. Scenerio: I have 2 textboxes on a form that I have to...

ASP / Active Server Pages

3029

>= in Case Statement - Stuck

by: Ryan | last post by:

I'm struggling with a Case statement. The problem I has is with doing >= I can use any value in there, but need to check if it's greater or equal to 1. I'm sure I'm missing something but can't...

Microsoft SQL Server

4791

Winsock - Empty Receiving Buffer

by: Ramiro Barbosa, Jr. | last post by:

All, In regards to the call below, I was wondering why is it that the 'szMessage' receiving buffer results in an empty one, but by printing 'ret' it indicates the number of bytes I am indeed...

C / C++

5730

Empty string

by: M D | last post by:

You know how you assume you know until you find out you don't know. Well, I typed into a function definition "..., new String("")). I know what I want. Everyone reading this knows what I want....

C# / C Sharp

3168

Removing Bad Words

by: Jim Carlock | last post by:

Looking for suggestions on how to handle bad words that might get passed in through $_GET variables. My first thoughts included using str_replace() to strip out such content, but then one ends...

PHP

3430

ObjectDataSource - problem inserting empty field into stored proce

by: Dabbler | last post by:

I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function...

ASP.NET

2847

ListView ItemUpdating OldValues and NewValues collections Empty (linqDataSource)

by: Marius Manolea | last post by:

Hi, I have a strange problem, I don't receive any modifications from listview in edit mode (OldValues & NewValues are empty) <asp:ListView ID="lvEditPersonRoles" runat="server"...

ASP.NET

2102

Empty folders

by: Per Juul Larsen | last post by:

Hi. My application creates empty libraries. How do I ensure that the user copies at least one or more picture files (.jpg) in each of the empty folders ? Result , no empty folders! regards pjl

Visual Basic 4 / 5 / 6

4760

How to handle NULL parameters,EMPTY parameters and ERROR HANDLING in stored procedure

by: Soniad | last post by:

Hello, I have stored procedure in which i am passing parameters (varchar(8000)), these parameters can have values or can be empty (''), i have written blocks of codes for each parameter , which...

Microsoft SQL Server

7242

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

7138

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

7418

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

7075

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

7508

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

3212

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1572

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...

C# / C Sharp

781

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

446

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

General