Dynamic Query Sql Server

Question

Dynamic Query Sql Server

Navigation

#1 by (4 votes)

3

Gentlemen, I have the following problem:

I have to set up a query that works with multiple views, for example, one of products or one of people. I'm doing some tests with this code:

declare @coluna nvarchar(max)
declare @variavel nvarchar(max)
declare @view nvarchar(max)
declare @sql nvarchar(max)

set @view = 'viewTeste'
set @coluna = 'Nome'

set @variavel ='t'

set @sql = 'select * from' + @view + ' as PFW join tbl_teste1 on PFW.ID = tbl_teste1.ID where '+ @coluna + ' like ' + '%' + @variavel + '%'

exec (@sql)

Where the user will pass the name of the view the column that wants to query and the variable

But this code is returning the following error:

Incorrect syntax near 't'.

This 't' is the variable that the citizen has passed.

How to solve?

sql sql-server

asked by anonymous 24.06.2016 / 22:11

1 answer

change image from png to jpg with php Receive variable real time ajax

score 4 · Accepted Answer

You need to fix your query on like,

For example;

select * from tabela where like '%dado%';

If the data is between '%%' in your case the query is being mounted without the '.

select * from Fluxo_Contas where Nome like %t%

When would it be

select * from Fluxo_Contas where Nome like '%t%'

Correct as follows.

declare @coluna nvarchar(max)
declare @variavel nvarchar(max)
declare @view nvarchar(max)
declare @sql nvarchar(max)

set @view = 'Fluxo_Contas'
set @coluna = 'Nome'

set @variavel = 'BANCO';

set @sql = 'select * from ' + @view + ' where '+ @coluna + ' like ' + '''%' + @variavel + '%'''

print @sql;

exec (@sql)