How to Use LIKE with Table variable

Question

How to Use LIKE with Table variable

tonyarp05 61903

SSCommitted

Points: 1515
More actions
August 26, 2011 at 5:32 am

#243894

Hi everyone,
I am using dynamic query.
Declare @testquery varchar(1000),
@test-2 varchar(200)
set @test-2 = '*Ma.01.01'
select @testquery =
'
select code,id from public where code like '% +@test + %' '
exec (@testquery)
I am getting following error
Msg 402, Level 16, State 1, Line 3
The data types %s and %s are incompatible in the %s operator
Could anyone help me to process how to use LIKE with table variable..
Thanks,
Tony

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

I don't see a table variable anywhere there. What's the definition of the table 'public'?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tonyarp05 61903 SSCommitted Points: 1515 More actions · Answer 2

I have mentioned the table for the example..

Thanks,

tony

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

I still don't see a table variable anywhere, nor is there any definition (create table) for the table public.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tonyarp05 61903 SSCommitted Points: 1515 More actions · Answer 4

@test-2 is the table variable.

Public table contains two columns.(Testcode and testid)

Using dynamic query

select @testquery =

'select testcode,testid from public where testcode like '% + @test-2 +%' '

exec (@testquery)

--testcode like '% + @test-2 +%' ' -- this Like condition is not working with table variable (@test)

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Test is not a table variable. It's a scalar variable, a 200 character varchar.

Declare @testquery varchar(1000),
@test varchar(200)
set @test-2 = '*Ma.01.01'

Can you post the CREATE TABLE statement for the table public please.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tonyarp05 61903 SSCommitted Points: 1515 More actions · Answer 6

CREATE TABLE PUBLIC

(

TestId INT,

TestCode Nvarchar(100)

)

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

Your code was a mess of string and quote-related errors.

With the table you gave me, this works.

Declare @testquery varchar(1000),

@test-2 varchar(200)

set @test-2 = '*Ma.01.01'

select @testquery = 'select TestCode, TestId from [public] where TestCode like ''%' +@test + '%'' '

exec (@testquery)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

calvo SSChampion Points: 12930 More actions · Answer 8

tonyarp05 61903 (8/26/2011)
Hi everyone,
Declare @testquery varchar(1000),
@test-2 varchar(200)
set @test-2 = '*Ma.01.01'
select @testquery =
'select code,id from public where code like '% +@test + %' '
I am getting following error
Msg 402, Level 16, State 1, Line 3
The data types %s and %s are incompatible in the %s operator

As Gail said, there are no table variables. Your problem is with how you're plugging your variable into the dynamic select statement.

Try this for the dynamic select, the wild card '%' should work appropriately.

'select code,id from public where code like ''%' + @test-2 + '%'''

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

calvo SSChampion Points: 12930 More actions · Answer 9

Three minutes too slow on a Friday morning.

Tony,

A great way to troubleshoot dynamic SQL related errors is to print the statement instead of trying to execute it.

Change exec (@testquery) to print @testquery and you'll see the select statement that is trying to be executed. Sometimes it makes obvious otherwise mysterious errors.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

tonyarp05 61903 SSCommitted Points: 1515 More actions · Answer 10

This doesn't work, I am getting an error

Msg 402, Level 16, State 1, Line 369

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

I tested mine, it runs fine.

Post the exact code that is failing for you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lonajonson33 Newbie Points: 9 More actions · Answer 12

lonajonson33

Newbie

Points: 9

August 26, 2011 at 6:18 am

#1373705

HELLO I AM NEW MEMBER.. HELLO EVERY ONE

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

tonyarp05 61903 (8/26/2011)
This doesn't work, I am getting an error
Msg 402, Level 16, State 1, Line 369
The data types varchar and varchar are incompatible in the modulo operator.

That means that you still don't have the quotes right because "%" is both a wild-card and a mathematical operator. The code Gail posted works just fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions