How to define a LIKE variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to define a LIKE variable

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-03-10 : 15:29:55

Hello.

I have a SP that looks at various things in the where clause. One of those items is a LIKE XX. When I try to pass this as variable i get no results verses typing in the actual value. Am I setting this up incorrectly?

I've shown the declarations as it is when i run it in the Query Analyzer. Is char the right format to use? Btw, the dates work fine.

Declare @SrtofDay datetime
Declare @EndofDay datetime
Declare @SpnCoat char (10)

Set @SrtofDay = '3/4/2004 6:00:00 AM'
Set @EndofDay = '3/4/2004 9:00:00 PM'
Set @SpnCoat = '3%'

WHERE CLAUSE:

WHERE
(prod.date_time BETWEEN @SrtofDay AND @EndofDay
and prod.stack_no LIKE @SpnCoat)

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-10 : 15:37:37

you need to declare @SpnCoat as a varchar; a char will pad the variable with spaces causing the LIKE clause to fail.

- Jeff