Can't find a word in a string

Question

Post reply

Can't find a word in a string

bill.humphrey

SSC Enthusiast

Points: 113
More actions
May 1, 2008 at 8:05 am

#103488

Hi
I'm trying to pull back records based on search criteria for a varchar(250) field:
search on the word "procedure"
from the following records:
Procedure A
This is a procedure
This is a policy
The search should bring back :
Procedure A
This is a procedure
Trouble is my SP is not finding the records using Like '%@variable%'
Here is my script to run the SP:
declare @ErrorDesc varchar(100)
declare @ErrorNo int
declare @RowsAffected int
declare @Title varchar(250)
set @Title = 'Procedure'
exec ListtblPoliciesProceduresBySearchCriteria @Title, @ErrorDesc output, @ErrorNo output, @ErrorNo output
Here is my SP:
alter PROCEDURE ListtblPoliciesProceduresBySearchCriteria
@PoliciesProceduresTitle varchar(250),
@ErrorDesc varchar(100) output,
@ErrorNo int output,
@RowsAffected int output
AS
DECLARE @lWhere as Varchar(1000)
DECLARE @lErrorNo INTEGER
DECLARE @lRowsAffected INTEGER
--Declare @PoliciesProceduresTitle varchar(250)
--set @PoliciesProceduresTitle = 'Procedure'
Begin
SELECT tblPoliciesProcedures.tblPoliciesProceduresId as tblPoliciesProceduresId,
tblPoliciesProcedures.PoliciesProceduresTitle as PoliciesProceduresTitle,
tblPoliciesProcedures.PoliciesProceduresInformation as PoliciesProceduresInformation,
tblPoliciesProcedures.LastUpdatedBy as LastUpdatedBy,
tblPoliciesProcedures.LastUpdatedOn as LastUpdatedOn,
'' as LastUpdatedOnText,
tblPoliciesProcedures.LastUpdatedCount as LastUpdatedCount
FROM tblPoliciesProcedures
Where tblPoliciesProcedures.PoliciesProceduresTitle like '%@PoliciesProceduresTitle%'
order by PoliciesProceduresTitle
select @lRowsAffected = @@ROWCOUNT, @lErrorNo = @@ERROR
IF @lErrorNo > 0
Begin
set @ErrorDesc = 'error with List of tblPoliciesProcedures'
set @ErrorNo = @lErrorNo
set @RowsAffected = 0
RETURN
End
Else
Begin
set @ErrorDesc = 'Get was Successful for tblPoliciesProcedures'
set @ErrorNo = 0
set @RowsAffected = @lRowsAffected
RETURN
End
end
GO

Viewing 6 posts - 1 through 5 (of 5 total)

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

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 1

Your syntax appears to be incorrect. Try changing

'%@PoliciesProceduresTitle%'

to

'%' + @PoliciesProceduresTitle + '%'

Edit:

Another question is how many rows are in the target table that you are searching?

GSquared SSC Guru Points: 260824 More actions · Answer 2

When you want to use a variable in a Like statement, use this:

where Column like '%' + @variable + '%'

Otherwise, the way you wrote it, it's looking for the string-literal "@variable". Not the value of the variable, the name.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

bill.humphrey SSC Enthusiast Points: 113 More actions · Answer 3

Many thanks for your help

I need a good Transact SQL book 🙂

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

You've already got one... Books Online 🙂

--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

GSquared SSC Guru Points: 260824 More actions · Answer 5

Actually, when I was first learning SQL, Books Online took too much for granted in many cases. Too many circular definitions, too much left out in too many cases.

I found SQL Server 2000 Bible, by Paul Nielsen, very, very helpful in getting started. Doesn't assume as much as BOL.

Once you've "got it", BOL is a great reference, but I personally didn't find it all that useful when first starting out.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon