dynamic TOP - 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)

dynamic TOP

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-04-08 : 03:14:06

Hi

I have the following stored procedure query...


SELECT        TOP (@TopItems) PdfFileName, ID, IsFetched
FROM            dbo.Cards
WHERE        (IsFetched IS NULL OR
                         IsFetched = 0) AND (PdfFileName <> N'')
ORDER BY DateAdded

I would like to change this so that if I pass @TopItems = 0 then I should retrieve all rows (not using the TOP xx part), but if I pass any value higher than 0, the TOP part should be used, how can I change this?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-08 : 05:20:25

[code]DECLARE @TopItems INT =0
IF ( @TopItems > 0)
BEGIN
SELECT TOP (@TopItems) PdfFileName, ID, IsFetched
FROM dbo.Cards
WHERE (IsFetched IS NULL OR IsFetched = 0) AND (PdfFileName <> N'')
ORDER BY DateAdded
END
ELSE
BEGIN
SELECT PdfFileName, ID, IsFetched
FROM dbo.Cards
WHERE (IsFetched IS NULL OR IsFetched = 0) AND (PdfFileName <> N'')
ORDER BY DateAdded
END
[/code]