CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rod
Rod

Reputation: 15457

sql cte if statement

This might be a bad idea but just wondering?

;with Products
AS
(
/* can you have an if statement in here? */
select * from products

/* or */

select * from products 
where condition

)

what i'm thinking is what if sometimes you have a search string or not. How do you make that a consideration in cte?

Or maybe it would be a better idea to have 2 cte in one procedure?

Upvotes: 2

Views: 390

Answers (2)

Sagar Dev Timilsina
Sagar Dev Timilsina

Reputation: 1380

table variable might also help you. Check if it helps..

DECLARE @tbl TABLE(id int,name varchar(500), .... )

if <@booleanexpression = 1> INSERT INTO @tbl select * from products else INSERT INTO @tbl select * from products where condition..

with cte as ( select * from @tbl )

select * from cte

Upvotes: 1

Tim Lehner
Tim Lehner

Reputation: 15251

If you're passing in a search string as a parameter, you can check that it is null or not all in one statement. For example:

select *
from MyTable
where MyColumn = @SearchString
    or @SearchString is null;

This will return records that match when the parameter is not null, and return all records when it is null.

As another option, you can always put case statements in your where clause.

Beyond that, if you truly need different queries, you can certainly branch with if BUT your query must be the very next statement after you declare the CTE. Thus you'd have to have a copy or your CTE and query in each branch of the if statement.

If you're thinking of passing an entire where clause and running it all as dynamic SQL (edit: meaning a non-parameterized concatenated string, not ORM-type sp_executesql), I would try refactoring to use any of the above methods first, as there are inherent problems with dynamic SQL. Dynamic SQL often looks clever and elegant at the outset, but should more often be seen as a last resort only when other options somehow turn out to be worse.

Upvotes: 5

Related Questions