Select records with blank values

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)

Select records with blank values

Author

Topic

divyaps
Starting Member

4 Posts

Posted - 2010-12-29 : 14:07:35

Hi,
I have a stored proc used on SSRS reports which has to return records with blank values for confirmation number field if a blank value is passed in or a record with the specified confirmation number if a confirmation number is passed in.

My sql right now is

(IsNull(@ConfirmationNumber,'') = '' or RoomRegistration.ConfirmationNumber like '%' + @ConfirmationNumber + '%')

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-12-29 : 14:18:19

What problem are you experiencing with your query? Does it return the wrong results (too few; too many)? Does it throw an error? Does it run too slow? We'd be glad to help but you need to give us a little more to go on. Help us to help you.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-29 : 14:18:49

I don't see a question only a statement.

Maybe this link will help?
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-29 : 15:54:01

WHERE ((@ConfirmationNumber IS NULL OR @ConfirmationNumber = '') AND (ConfirmationNumber IS NULL OR ConfirmationNumber = ''))
OR (ConfirmationNumber LIKE '%' + @ConfirmationNumber + '%' AND @ConfirmationNumber > '')

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

divyaps
Starting Member

4 Posts

Posted - 2010-12-30 : 09:16:33

My question is - my above sql query is returning all results when a blank or null value is passed. How do I modify it to return only the records with blank confirmation number when a blank value is passed and return all values when a null value is passed?
I even tried this variation
(ConfirmationNumber LIKE CASE WHEN @ConfirmationNumber ='' THEN @ConfirmationNumber
WHEN @ConfirmationNumber is NULL THEN '%%'
ELSE '%' + @ConfirmationNumber + '%' END )
still doesnt work the way I wanted..Please advise

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-30 : 12:11:57

Since you didn't provide and DDL, DML or expected output we can only guess at what your scheam and data might look like. Took a guess at one way you could do this:

DECLARE @T TABLE (ID INT IDENTITY(1,1), ConfirmationNumber VARCHAR(10))
INSERT @T VALUES
('ABCD'),
('1234'),
(''),
('ACC'),
('DD4T'),
('DD4'),
('KKI89'),
(''),
('0987')


DECLARE @ConfirmationNumber VARCHAR(10)
SET @ConfirmationNumber = ''
SET @ConfirmationNumber = '0'
SET @ConfirmationNumber = NULL

SELECT *
FROM @T
WHERE 
 @ConfirmationNumber IS NULL
 OR 
 (
    @ConfirmationNumber = '' 
    AND ConfirmationNumber = ''
 )
 OR 
 (
    @ConfirmationNumber <> ''
    AND ConfirmationNumber LIKE '%' + @ConfirmationNumber + '%'
 )

divyaps
Starting Member

4 Posts

Posted - 2010-12-30 : 12:39:29

Lamprey,Thanks a lot for your reply. It worked for me :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources