Store Procedure (case in where clause)

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)

Store Procedure (case in where clause)

Author

Topic

shulink
Starting Member

12 Posts

Posted - 2007-11-01 : 14:19:32

Hi, I need help on this store procedure. I tried to put a case in the where clause. The Gender could be 0, 1 or 2. I want to have the case only when gender is 0 or 1, but not 2. But it gives me error. And if I try to build a dynamic on where query, the I get other error on the temporary table @Users table saying it is not declare. Can anyone help me out? Thanks a lot.

CREATE PROCEDURE dbo.GetUsers
(@UserId uniqueidentifier,
@Gender Int,
@PageNumber Int,
@UsersPerPage Int,
@HowManyUsers INT OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @Users TABLE
(RowNumber Int,
UserId uniqueidentifier,
LastName Varchar(50),
FirstName Varchar(50))

INSERT INTO @Users

SELECT ROW_NUMBER() OVER (ORDER BY P.UserId),
P.UserId, P.LastName, P.FirstName
FROM dbo.Profile P
WHERE P.UserId <> @UserId
CASE WHEN @Gender =1 THEN 'AND P.GenderId = 1'
WHEN @Gender = 0 THEN 'AND P.GenderId = 0'
END

SELECT @HowManyUsers = COUNT(UserId) FROM @Users
SELECT UserId, LastName, FirstName
FROM @Users
WHERE RowNumber > (@PageNumber - 1)*@UsersPerPage
AND RowNumber <= @PageNumber * @UsersPerPage

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-01 : 14:24:32

You don't need CASE for this. You can use IN or OR.

WHERE p.GenderId = @Gender AND p.GenderId IN (0, 1)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shulink
Starting Member

12 Posts

Posted - 2007-11-01 : 14:50:52

hi, thank you so much for your help. I have never thought of that. I used the following
Where (P.Gender = @GenderId OR @GenderId = 2), since 0 = male, 1 = female, 2 = both, so when the genderId is 2, I will omit this condition in the first place. Thanks again, it took me two days to try to make up the dynamic query and play around with the case statement, and still ended up with syntax error, and didn't think about this. You save my life.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources