July 24, 2002 at 8:59 am
Hello all!
I'm a rank SQL beginner, but with some coding experience elsewhere. I have a stored procedure that I imagine is fairly standard: It takes one user entry that represents a string of keywords and a second user entry "AND" or "OR" and converts them into a SQL call that returns a result set that can be displayed. I've appended the code at the bottom.
The problem is that while the code appears to work - a result set with the correct number of rows is returned - for some reason I cannot get it to display the columns. To make it worse, if I bypass the user entry by setting the value of the passed variable directly in code (set @searchin=...), everything works and the resultset looks great.
As a potential clue to the problem, I had to add a bunch of ltrim's and rtrim's to the code to get it to work in 'bypass mode', though. My guess is that the problem has something to do with the way varchar's are manipulated, but I can't figure out the principle behind it, so I'm only guessing.
Any and all help appreciated! (BTW, the web site itself is kinda interesting: it's a searchable database of 150,000 images of the Soviet invasion of Afghanistan taken in the 80's and 90's. A beta is available at http://cfserver.williams.edu/amrc - for your viewing pleasure.)
- jonathan
--SNIP--
BrowseSP
(@searchIn varchar(1000),@bool varchar(3))
AS
declare @search as varchar(3000)
declare @strPos as int
set @searchIn=ltrim( rtrim (@searchIn))+' '
set @strPos=charindex(' ',@searchIn)
/*set identity functions for and and or*/
if @bool='AND' begin set @search = '(1=1)' end else begin set @search = '(1=0)' end
while (@strPos <> 0) begin
set @search = @search + ' ' + ltrim(rtrim(@bool)) + ' (keyword LIKE ''%'+ left(@searchIn, @strPos-1) + '%'')'
set @searchIn= ltrim(rtrim(right( @searchIn, len( @searchIn) - @strPos +1)))
set @strPos=charindex(' ',@searchIn)
end
if @searchIn<>'' begin set @search = @search + ' '+ @bool+ ' (keyword LIKE ''%' + @searchIn + '%'')' end
set @search='SELECT * FROM dbo.PhotosWithKeywords WHERE (' +ltrim(rtrim(@search)) + ' )'
execute (@search)
July 25, 2002 at 6:22 am
Try this.
--SNIP--
BrowseSP
(@searchIn varchar(1000),@bool varchar(3))
AS
SET NOCOUNT ON
DECLARE @search as varchar(3000)
DECLARE @strPos as int
SET @search = ''
SET @searchIn=ltrim(rtrim(@searchIn))
SET @bool = ltrim(rtrim(@bool))
SET @strPos=charindex(' ',@searchIn)
WHILE (@strPos > 0)
BEGIN
SET @search = @search + ' (keyword LIKE ''%'+ left(@searchIn, @strPos-1) + '%'') ' + @bool
SET @searchIn= ltrim(right(@searchIn, len(@searchIn) - @strPos))
SET @strPos=charindex(' ',@searchIn)
END
SET @search = @search + ' (keyword LIKE ''%' + @searchIn + '%'') '
SET @search='SELECT * FROM dbo.PhotosWithKeywords WHERE (' + @search + ')'
EXECUTE (@search)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply