CODEWITHSUNDEEP
sqlsql-servervariablessql-like
user1879840
user1879840

Reputation: 23

Using SQL variable in like statement

I have done much research on this but just cannot get it right. I am trying to query an audit trail using the below:

declare @batchid char (30)
select @batchid='13857584'  --enter batch id here
declare @occurred int 
select @occurred='5'
print 'Batch is in system, check MRN and encounter. User also noted below'
select a.encounter, a.mrn, a.facility, c.fullname, t.action_desc, a.occurred, a.remark 
from audit..audit_trail a (nolock)
join cabinet..users c (nolock)
on a.userinstanceid=c.userinstanceid
join audit..action_table t (nolock)
on a.action=t.action
where a.REMARK like '%' +  @batchid  + '%'
and OCCURRED > GETDATE()-@occurred
order by a.occurred

for some reason it will never return the results i want (no results). But when i run it specifically for the batch id like below (instead of @batchid)

declare @batchid char (30)
select @batchid='13857584'  --enter batch id here
declare @occurred int 
select @occurred='5'
print 'Batch is in system, check MRN and encounter. User also noted below'
select a.encounter, a.mrn, a.facility, c.fullname, t.action_desc, a.occurred, a.remark 
from audit..audit_trail a (nolock)
join cabinet..users c (nolock)
on a.userinstanceid=c.userinstanceid
join audit..action_table t (nolock)
on a.action=t.action
where a.REMARK like '%' +  '13857584' + '%'
and OCCURRED > GETDATE()-@occurred
order by a.occurred

it works perfectly. I tried adding quotes after and before percentage signs but i just cant get it right. Any help appreciated. SQL 2008

Upvotes: 1

Views: 5064

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

You care declaring batchid as a char:

declare @batchid char (30)
select @batchid='13857584'  --enter batch id here

In fact, the batchid is being set to something like:

'13857584______________________'

The underscores are intended to show the space character, not an actual underscore.

Try changing it to varchar():

declare @batchid varchar(30);
select @batchid = '13857584';  --enter batch id here

Upvotes: 5

Related Questions