Case Statement in WHEN clause - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Case Statement in WHEN clause

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-09-03 : 03:48:50

Hi all

I am doing a select and putting everything into a recordset, so that then I can do the paging on it. However, in the where, I wish to include a clause, that if the @pagesize = 0 (means return all records), then I will not execute the where clause, but if the @pagesize > 0 then I want to execute.

I came up with something like this which is not working at the moment:-

SELECT * FROM PagesRN
CASE @PageSize
WHEN 0 THEN --do nothing, ie return all records
ELSE -- return the records by the PageNum and the PageSize
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize

Can you please tell me what I have wrong

Thanks for your help and time

Johann

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-03 : 04:34:57

May be your query can be modified as:

If @PageSize > 0
BEGIN
SELECT * FROM PagesRN WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
END

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-09-03 : 05:01:19

try this


SELECT * FROM PagesRN
WHERE (@PageSize = 0 OR 
		        (RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
			AND @PageNum * @PageSize)
		)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources