Is a T-SQL coniditional TOP clause possible?

Question

I want to dynamically use TOP or not sort of like this...

SELECT @SomeNumber CASE WHERE 0 THEN TOP 5 COLUMNNAME
                        ELSE COLUMNNAME
                   END
FROM TABLE

Answer 1

I don't think this is possible because TOP is applied on not just a column but the whole row. You would have to create two different select statements and put them in a IF ELSE construct.

Answer 2

Short answer is no, not the way you have it.

You can however use IF to test and run a different query:

IF (@SomeNumber = 0)
BEGIN
   SELECT TOP 5 ColumnName FROM Table
END
ELSE
BEGIN
   SELECT ColumnName FROM Table
END

Answer 3

I've just used something like this:-

Declare @SQL nvarchar(max), @Params nvarchar(max)
set @Params = N''
Set @SQL = N'SELECT ' + Cast(@SomeNumber as varchar) + ' CASE WHERE 0 THEN TOP 5 COLUMNNAME
                        ELSE COLUMNNAME
                   END
FROM TABLE'

exec sp_executesql @SQL, @Params

Answer 4

Two options: conditional SQL or dynamic SQL.

(1) Conditional:

IF @SomeNumber = 0
    SELECT TOP 5 COLUMNAME FROM TABLE
ELSE
    SELECT COLUMNAME FROM TABLE

(2) Dynamic: build up the query in a varchar() and pass it to sp_execute

Answer 5

I don't think you can.

You could either use dynamic SQL:

Declare @int int

set @int = 10

exec ('Select top '  + @int + ' * From Customers')

Or you could set rowcount

if (@someNumber != 0)
begin
set rowcount 5
end

select * From Customers

set rowcount 0

Answer 6

Hi

I hope to have understood your problem: you want to select the TOP 5 rows if you pass @SomeNumber = 0 else select all th etable rows

As a first straight implementation you can do something like that

declare @SomeNumber as int

set @SomeNumber = 5
-- set @SomeNumber = 1

SELECT TOP (SELECT @SomeNumber) COLUMNNAME FROM MYTABLE

you can change the parameter value in order to have how many rows you want

---

Otherwise i suggest you to implement a stored procedure (and maybe you already did that, otherwise you can follow the next steps in order to do it)

CREATE procedure [dbo].[TOPCLAUSE]

    -- clause parameter
    @SomeNumber as integer

AS

IF @SomeNumber = 0 
BEGIN
    SELECT TOP 5 COLUMNNAME FROM MYTABLE
END
ELSE
BEGIN
    SELECT COLUMNNAME FROM MYTABLE
END

GO

Then you can call

exec [dbo].[TOPCLAUSE] 0

exec [dbo].[TOPCLAUSE] 1

I probably not answered your question but let me know if it helped you