May 1, 2008 at 8:05 am
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
May 1, 2008 at 8:08 am
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?
May 1, 2008 at 8:09 am
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
May 1, 2008 at 8:15 am
Many thanks for your help
I need a good Transact SQL book 🙂
May 1, 2008 at 8:41 am
You've already got one... Books Online 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
May 1, 2008 at 10:03 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply