Basic TSQL Question - 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

Basic TSQL Question

Author

Topic

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2007-12-29 : 02:47:13

Hi

I have a basic SP query which filters a column (Name), but can also be filtered by another column if the user provides a parameter (Post Code). How can I write this in one TSQL stament rather than having two staments, but ignore PostCode if they have not provieded it.

SELECT Name FROM Customers WHERE Name LIKE @Name AND PostCode = @PostCode

If PostCode = '' in the above example then nothing is returned. Im sure there is a way to ignore the second filter

Many thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-29 : 02:51:14

[code]SELECT Name
FROM Customers
WHERE (
@Name IS NULL
OR Name LIKE @Name
)
AND (
@PostCode IS NULL
OR PostCode = ''
OR PostCode = @PostCode
)[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-29 : 03:05:20

and Name LIKE @Name is equal to Name = @Name

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-29 : 03:09:16

quote:
Originally posted by madhivanan

and Name LIKE @Name is equal to Name = @Name

Madhivanan

Failing to plan is Planning to fail

well it depends what is the value of @Name right ? if @Name's value is concat with '%' then it will be a LIKE

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-29 : 03:14:27

quote:
Originally posted by khtan

quote:
Originally posted by madhivanan

and Name LIKE @Name is equal to Name = @Name

Madhivanan

Failing to plan is Planning to fail

well it depends what is the value of @Name right ? if @Name's value is concat with '%' then it will be a LIKE

KH
[spoiler]Time is always against us[/spoiler]

Yes that makes sense

Madhivanan

Failing to plan is Planning to fail

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2007-12-29 : 15:56:42

Thanks guys... worked a treat!

Happy Christmas and new years to you all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources