Can I form a WHERE in a variable of @?

3

A question, Can I put the condition (WHERE) in a variable? where does it contain all the conditions and use it at the end of my SP? If it is possible, how could it be done?

something like

SELECT * FROM TABLA WHERE fecha = @condiciones;

Where @conditions contain something like:

 @condiciones = '2018-08-08' AND condicion2 = 'www' AND condicion3 = 'wwwwww';
    
asked by Richard 06.08.2018 в 22:11
source

2 answers

3

Yes, but conceptually it is something totally different. First of all, when you do:

SELECT * FROM Tabla WHERE Columna = @Valor

It is nothing more than a normal, parameterized query. The engine takes it, compiles it and can, if it so determines, make an execution plan that optimizes the query to minimize server resources and at the same time improve performance.

What you are looking for can be done without a doubt, but through something that is known as dynamic queries . In other words, judgments that you create dynamically. The simplest way, it could be something like this:

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = 'SELECT * 
                      FROM TABLA 
                      WHERE COLUMNA_VARCHAR=''VALOR'' 
                      AND COLUMNA_NUMERICA=1'

EXEC(@SQL)

Observations:

  • You can not combine a traditional sentence with a dynamic are different things
  • the statements you write enclosed in quotes as you would normally write it but you must escape the internal quotes with double quotes
  • This type of query can not be optimized by the engine, which we mentioned at the beginning
  • If we want to parameterize the query, one way is to concatenate the values of each parameter by means of a variable, for example .. COLUMNA_VARCHAR = ''' + @Valor + '''....' , but NO is the appropriate form since this is susceptible to SQL Injection . The correct way is to use sp_execute_sql and the parameterization of each parameter.
answered by 07.08.2018 в 06:07
1

Of course you can, this would be a way to do it:

Declare @condiciones varchar(max)= 'WHERE campo1 = valor1 AND campo2 = valor2'

And to execute the query, you would do the following:

EXEC('SELECT * FROM TABLA' [email protected])

The above would be equivalent to executing directly:

SELECT * FROM TABLA WHERE campo1 = valor1 AND campo2 = valor2
    
answered by 06.08.2018 в 22:58