Dynamic Sql Server Query

Question

Gentlemen, I have the following problem:

I have to set up a consultation that works with various 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 view name to the column they want 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 passed.

How to solve?

Answer 1

You need to fix your query in the like ,

For example;

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

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

select * from Fluxo_Contas where Nome like %t%

When would it have to 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)