Using IF statement after a WITH clause

Question

I want to create an alias for a SELECT statement, but then I want to limit the resultset with a TOP clause. If the procedure TopCount parameter is bigger than 0, I want to put limit result set with TOP clause.

But after the WITH clause, the IF statement is producing an error. Can I continue with IF or different than the SELECT statement after the WITH clause?

WITH
   alias AS (...)
IF @TopCount>0
   SELECT TOP (@TopCount) * FROM alias;
ELSE
   SELECT * FROM alias;
GO

score 5 · Accepted Answer · answered Jul 16 '18 at 11:49

5

No, you can't do that. Your best bet would be to give your top variable a very high default value.

CREATE PROC dbo.if_branch (@top INT = 2147483647)
AS 
BEGIN

WITH d AS 
    (
        SELECT *
        FROM sys.databases AS ds
        WHERE ds.name = 'tempdb'
    )
SELECT TOP (@top) *
FROM d;

END

answered Jul 16 '18 at 11:49

Erik Darling

40,781
14
130
456

Can you use a BIGINT here? We have tables too large for INT max. – Joshua Jul 16 '18 at 19:46
@Joshua yes, but is anyone really selecting that many rows? What would a person conceivably do with all that, unless it's a data export? – Erik Darling Jul 16 '18 at 19:48
Indeed; when I asked for all the rows I didn't want only the first 4 billion. – Joshua Jul 16 '18 at 20:21

score 2 · Answer 2 · answered Jul 16 '18 at 11:50

2

WITH declares a CTE, which is something you can use in just 1 statement (like a SELECT, INSERT, UPDATE, etc.).

An IF is a control flow mechanic that separates statements (or blocks of statements) so you can't declare a CTE and conditionally refer to it on multiple statements.

You can try setting the @TopCount value to a high enough number before the SELECT.

IF @TopCount IS NULL OR @TopCount < 1
    SET @TopCount = POWER(2, 30) -- A big enough number (careful with overflows!)

WITH
   alias AS (...)
SELECT TOP (@TopCount) * FROM alias;

answered Jul 16 '18 at 11:50

EzLo

3,296
2
13
24

1

you may want to use CONVERT(int, POWER(2.0, 31) - 1), or just the maximum value for an int, which is 2147483647 – Hannah Vernon Jul 16 '18 at 12:58
Thank you for your fast answer. @sp_BlitzErik's answer is fit for clear to understand. It isn't checking topCount is greated than 0 in every execution. It isn't calculating limit of int type. Again thank you for your answer and I learnt POWER with your answer. – uzay95 Jul 16 '18 at 16:16

score 2 · Answer 3 · edited Jul 17 '18 at 17:17

2

The TOP parameter is an expression, not a literal. Try to use:

WITH alias
  AS (...)
SELECT TOP (CASE WHEN @TopCount>0 THEN @TopCount ELSE 2147483647 END) *
  FROM alias;

PS.: I have not tested this trick with a variable, but the TOP parameter obtained from a subquery works perfectly.

PPS.: I tested with a user variable. It works.

edited Jul 17 '18 at 17:17

Peter Mortensen

350
2
10

answered Jul 16 '18 at 12:42

Akina

19,866
2
17
21

score 0 · Answer 4 · edited Jul 17 '18 at 17:11

You can run your calculation beforehand in a case statement.

I've declared @topcount in this statement for ease of testing, but so you can set it and test it:

declare @TopCount int

SELECT
    @TopCount = CASE
        WHEN ISNULL(@TopCount,0) > 0
            THEN @TopCount
            ELSE count(1) END
    FROM alias

select top (@TopCount) *
from alias

You can use the final select in a with statement if you need it for a CTE from here quite easily.

Using IF statement after a WITH clause

4 Answers4