case statement in where clause of stored procedure

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 statement in where clause of stored procedure

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-29 : 02:01:12

Hi all,

Can case statement or if statement be used in stored procedure's where clause. I have following stored procedure which has @custType parameter. I want to filter results based on this parameter.

ALTER PROCEDURE [dbo].[GetUsersByUserName]
(
@UserName VARCHAR(50),
@custType int
)
AS

SET NOCOUNT ON

SELECT
[UserId],
[UserName],
[Password],
[FirstName],
[LastName],
[Gender],
[Email],
[FAX],
[DOB],
[IsEnable],
[PrefferedLanguage],
[ReceiveEmails],
[IsWholesaler],
[Activekey],
[IsActive],
[EndDate],
[CreatedBy],
[CreatedOn],
[ModifiedBy],
[ModifiedOn]
FROM
[Users]
WHERE
UserName like '%' + @UserName + '%'

AND [IsActive] = 1
ORDER BY
[UserName]

@custType is dynamic and can have 4 values

if @custType = 1 all rows to be displayed
if 2 then iswholesaler = 1 data should be displayed
if 3 then iswholesaler =0 rows should be displayed

so where clause need to have case statement

Please help me on this.

Regards,
Asif Hameed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 02:09:44

quote:
if 2 then iswholesaler = 1 data should be displayed
if 3 then iswholesaler =0 rows should be displayed

what do you mean by this ?
what is the condition ?

@custType = 2 and iswholesaler = 1 ? What happen when this is fulfilled ?
@custType = 3 and iswholesaler = 0 ? What happen when this is fulfilled ?

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

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-29 : 02:14:28

if @custType = 2

then this should be added in where clause

where iswholesaler = 1

if @custType = 3

then this should be added in where clause

where iswholesaler = 0

where iswholesaler is a column in the table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 02:17:14

[code]
WHERE UserName like '%' + @UserName + '%'
AND [IsActive] = 1
AND (
(@custType = 1)
OR (@custType = 2 AND iswholesaler = 1)
OR (@custType = 3 AND iswholesaler = 0)
)
[/code]

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

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-29 : 02:18:16

This will work for you!

select * from Users where

case when @custType =1 then 1 else 0 end =1
or
iswholesaler = case when @custType =2 then 1 else 0 end

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-29 : 02:23:26

and how to get this

if @custType = 1 all rows to be displayed means no filtering based on iswholesaler.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 02:25:12

quote:
Originally posted by sparrow37

and how to get this

if @custType = 1 all rows to be displayed means no filtering based on iswholesaler.

see the query i posted on 07/29/2009 : 02:17:14

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

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-29 : 03:05:02

Hi KH,

will it display all records or only the one matching one of the OR conditions ?

Regards,
Asif

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 03:09:18

it depends on the value of @custType pass in.

Give it a try . .

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

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-29 : 03:48:33

Hi KH,

I need to add one more condition. I am getting error on 3rd case in where clause. I need to apply filter on the basis of another table wholesaler. If its one bit field ( iswholesaler =1) then bring data from user table. user and wholesaler table have relationship ( one to one ) on userid

ALTER PROCEDURE [dbo].[GetUsersByUserName]
(
@UserName VARCHAR(50),
@custType int
)
AS

SET NOCOUNT ON

--DECLARE @UserName varchar(50)
--set @UserName = ''
SELECT
[UserId],
[UserName],
[Password],
[FirstName],
[LastName],
[Gender],
[Email],
[FAX],
[DOB],
[IsEnable],
[PrefferedLanguage],
[ReceiveEmails],
[IsWholesaler],
[Activekey],
[IsActive],
[EndDate],
[CreatedBy],
[CreatedOn],
[ModifiedBy],
[ModifiedOn]
FROM
[Users]

WHERE
UserName like '%' + @UserName + '%'
--and iswholesaler = case when @custType =1 then 1 else 0 end

AND (
(@custType = 0)
OR (@custType = 1 AND iswholesaler = 1)
OR (@custType = 2 AND iswholesaler = 1 AND (if Exists(select * from [WholeSalers] where [userID] = @userID ))) // how to fix this
OR (@custType = 3 AND iswholesaler = 0)
)

AND [IsActive] = 1
ORDER BY
[UserName]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 03:51:53

[code]AND
(
(@custType = 0)
OR (@custType = 1 AND iswholesaler = 1)
OR (@custType = 2 AND iswholesaler = 1 AND Exists(select * from [WholeSalers] where [userID] = @userID) )
OR (@custType = 3 AND iswholesaler = 0)
)[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources