T-SQL – Using IF Statement After a WITH Clause

t-sql

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

Best Answer

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
Related Question