CODEWITHSUNDEEP
sql-servert-sqlvariables
Aman Sharma
Aman Sharma

Reputation: 321

How to pass sql variable to like condition SQL Server 2012

I am creating dynamic sql query and passing variable to like statement here is my code 

 DECLARE @DBname1 VARCHAR(200),@query VARCHAR(200),@tname VARCHAR(200);
  SET @DBname1='core'
  SET @tname = 'abc'
 SET @tname ='''+ '%' + @tname +'''+ '%'
 PRINT @tname 
SET @query= 'Select * FROM '+@DBname1+ '.information_schema.tables where TABLE_NAME like '+ @tname 
PRINT @DBname1
PRINT @query
EXEC (@query)

problem is i m not able to pass single quote to like variable , above code show error message

The data types varchar and varchar are incompatible in the modulo operator

If I change my code to 

SET @tname ='%' + @tname + '%'

then the single quote is removed from variable and become TABLE_NAME like %test% and it does not execute because a single quote is missing.

So I want to know how I can add single quote to sql variable before passing it to like condition or how to concatenate that.

Upvotes: 1

Views: 544

Answers (2)

Stephen
Stephen

Reputation: 1542

You can also use CONCAT like this: 

SET @query= CONCAT('Select * FROM ', 
               @DBname1, 
               '.information_schema.tables where TABLE_NAME like ', 
               '''%', @tname, '%''')

Upvotes: 1

Sankar
Sankar

Reputation: 7117

you should do like this...

DECLARE @DBname1 VARCHAR(200),@query VARCHAR(200),@tname VARCHAR(200);
SET @DBname1='core'
SET @tname = 'abc'
PRINT @tname 
SET @query= 'Select * FROM '+@DBname1+ '.information_schema.tables where TABLE_NAME like ''%'+ @tname + '%'''
PRINT @DBname1
PRINT @query
EXEC (@query)

Upvotes: 2

Related Questions