How to pass sql variable to like condition SQL Server 2012

Question

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.

Sankar · Accepted Answer · 2017-05-11 06:24:30Z

2

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)

answered May 11, 2017 at 6:24

Sankar

7,1772 gold badges36 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aman Sharma Over a year ago

Thanks it works for me , i am searching for this from last 2 days .. Thanks again.

Sankar Over a year ago

Glad about that :)

Stephen · Accepted Answer · 2017-05-11 06:27:57Z

1

You can also use CONCAT like this:

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

answered May 11, 2017 at 6:27

Stephen

1,5421 gold badge9 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to pass sql variable to like condition SQL Server 2012

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related