Case When on Where condition - 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

General SQL Server Forums

New to SQL Server Programming

Case When on Where condition

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2011-02-21 : 03:45:43

Hi all,

How to use case When condition on Where clause

Devart
Posting Yak Master

102 Posts

Posted - 2011-02-21 : 03:54:15

Hello,

For example:

CREATE TABLE t_table(
id INT,
txt VARCHAR(255)
)

GO

INSERT INTO t_table (id,txt) VALUES(1,'string_1');
INSERT INTO t_table (id,txt) VALUES(2,'string_2');
INSERT INTO t_table (id,txt) VALUES(3,'string_3');

GO

DECLARE @id INT

--SET @id=1

SELECT *
FROM t_table
WHERE id = CASE WHEN @id IS NULL THEN id ELSE @id END

Result for @id IS NULL:

1 'string_1'
2 'string_3'
3 'string_3'

Result for @id=1:

1 'string_1'

Best regards,

Devart,
SQL Server Tools:
dbForge Data Studio
dbForge Schema Compare
dbForge Data Compare
dbForge SQL Complete

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources