Execute AND statement dependent on a value of a variable

Question

Execute AND statement dependent on a value of a variable

Navigation

#1 by (3 votes)

0

In a stored procedure I want to add an AND statement to the query depending on the value of a variable.

DECLARE @name as varchar(50)
SET @name = ''
SELECT * FROM Tabla T
WHERE T.campo = 'valor'

Here I would like to add an AND statement to the query depending on the value of @name. If it is not empty, add the statement

AND T.Name like '%'+ @name + '%'

sql condiciones sql-server

asked by Alejandro 18.12.2017 в 19:25

source

1 answer

Calculate the difference between hours in SQL Infinite loop in PHP

score 3 · Answer 1

I assume that by empty , you really mean an empty string, and not NULL . In that case, to avoid a solution that wraps dynamic SQL, you can simply add a condition in the SQL for when @name is empty:

SELECT * FROM Tabla T
WHERE T.campo = 'valor'
  AND (LEN(@name) = 0 OR T.Name like '%'+ @name + '%')

But take into account that if T.Name does not allow values NULL , then it is not even necessary to add the additional condition. Simply add the condition as you have it:

SELECT * FROM Tabla T
WHERE T.campo = 'valor'
  AND T.Name like '%'+ @name + '%'

... I should return the correct results, even if @name is empty, because T.Name like '%%' will evaluate true for any value of T.Name (other than NULL ).