CODEWITHSUNDEEP
sqlsql-server-2008-r2
user194076
user194076

Reputation: 9017

Use top based on condition

I have a parameter in my stored procedure that specifies number of rows to select. (Possible values: 0-100. 0 Means Select All rows)

For example @Rows = 5;

Then I can do this: 

Insert into @MyTableVar   
   Select Top(@Rows) * 
   from myTable

Now, as I said before if 0 is supplied I need to return all rows.
This is a pseudo-code of what I need: 

if (@Rows=0) then select * else select top(@Rows) *

I found out that there's SET ROWCOUNT that accepts 0 to return ALL rows, but I need to do an insert into a table variable which is not supported by ROWCOUNT.

Is it possible to achieve this without dynamic sql?

(I understand that I can write a simple if else statement and duplicate query, but I have pretty complex queries and there are lots fo them, I just want to avoid code duplication)

Upvotes: 0

Views: 114

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

One way is to just put a big number in:

set @Rows = 5;
declare @RowsToUse = (case when @Rows = 0 then 1000000000 else @Rows end);

select top(@RowsToUse) * from myTable

Upvotes: 1

Lamak
Lamak

Reputation: 70638

First of all, you are missing the ORDER BY clause, since you are using TOP. You could do this:

SET @Rows = 5;

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(ORDER BY Id) --put the right order here
    FROM myTable
)
INSERT INTO @MyTableVar
SELECT YourColumns
FROM CTE
WHERE RN <= @Rows OR @Rows = 0

Upvotes: 1

Related Questions