Incorrect syntax near the keyword 'between'.

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)

Incorrect syntax near the keyword 'between'.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-28 : 08:20:45

Zak A writes "Each time I run this query I get this Incorrect syntax near the keyword 'between'. and i can't seem to find what is causing this.
Many thank

ALTER Procedure procAdvanceInfoSearch
@searchstring Varchar(1500)=null,
@searchCategory Varchar(100) = NuLL,
@searchSubCategory Varchar(100)= NuLL,
@searchSDate datetime = NuLL,
@searchEDate datetime = NuLL,
@searchAll bit = 0

AS
/* SET NOCOUNT ON */
BEGIN

DECLARE @orig_searchstring varchar(100)
DECLARE @SQLStatement varchar(5000)
DECLARE @strCategoryWhere varchar(100)
DECLARE @strSubCategoryWhere varchar(100)
DECLARE @strDateWhere varchar(100)
DECLARE @strSubSelWhere varchar(5000)
DECLARE @separator char(1)
DECLARE @separator_position int -- This is used to locate each separator character
DECLARE @search_keyword varchar(30) -- this holds each array value as it is returned
DECLARE @first_one bit -- set to false if there is more than one keyword in @searchstring

set @orig_searchstring = @searchstring
set @searchstring = @searchstring + ' '

IF (@searchCategory is not null)
BEGIN
SET @strCategoryWhere = ' AND (CatIDLink IN( ' + CONVERT(VARCHAR(20),@searchCategory)+ '))'
END

IF (@searchSubCategory is not null)
BEGIN
SET @strSubCategoryWhere = ' AND (SubCatIDLink IN( ' + CONVERT(VARCHAR(20),@searchSubCategory)+ '))'
END

IF (@searchSDate is not null)
BEGIN
SET @strDateWhere = 'AND (UpdatedDate between ''' + Convert(VARCHAR(10), + @searchSDate,102)+ ''' and '''+ Convert(VARCHAR(10), @searchEDate,102) + ''')'
END
IF (@searchstring is not null)
IF (@searchAll = 0)

BEGIN
SET @strSubSelWhere = ' WHERE '
-- now parse @searchstring which contains the keywords seperated by spaces
SET @separator = ' '
SET @first_one = 1

begin

SET @strSubSelWhere = @strSubSelWhere + ' (FREETEXT(tblInformation.*, ' + @search_keyword + ')''))'

end
END
Else

SET @strSubSelWhere = ' WHERE '
-- now parse @searchstring which contains the keywords seperated by spaces
SET @separator = ' '
SET @first_one = 1
while patindex('%' + @separator + '%' , @searchstring) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @searchstring)
select @search_keyword = '"' + left(@searchstring, @separator_position - 1) + '"'

IF (@first_one <> 1)
BEGIN
SET @strSubSelWhere = @strSubSelWhere + ' AND '
END
ELSE
BEGIN
SET @first_one = 0 --false
END

SET @strSubSelWhere = @strSubSelWhere + ' (contains(tblInformation.*, ''formsof(inflectional, ' + @search_keyword + ')''))'

-- This replaces what we just processed with an empty string
select @searchstring = stuff(@searchstring, 1, @separator_position, '')
end

execute ('SELECT dbo.tblInfoCategorisation.CatIDLink, dbo.tblInfoCategorisation.SubCatIDLink, dbo.tblInformation.InformationID, dbo.tblInformation.Title,
dbo.tblInformation.Summary, dbo.tblInformation.Content, dbo.tblInformation.UpdatedDate
FROM dbo.tblInformation INNER JOIN
dbo.tblInfoCategorisation ON dbo.tblInformation.InformationID = dbo.tblInfoCategorisation.InfoIDLink'
+ @strSubSelWhere + @strCategoryWhere + @strSubCategoryWhere + @strDateWhere )
Print 'SELECT dbo.tblInfoCategorisation.CatIDLink, dbo.tblInfoCategorisation.SubCatIDLink, dbo.tblInformation.InformationID, dbo.tblInformation.Title,
dbo.tblInformation.Summary, dbo.tblInformation.Content, dbo.tblInformation.UpdatedDate
FROM dbo.tblInformation INNER JOIN
dbo.tblInfoCategorisati

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-28 : 09:05:26

Print out the string you are executing and it'll probably be obvious.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-28 : 09:23:49

Try putting a space before the 1st "AND" like below, if that doesnt work do as nr suggested and then work from there

SET @strDateWhere = ' AND (UpdatedDate between ''' + Convert(VARCHAR(10), + @searchSDate,102)+ ''' and '''+ Convert(VARCHAR(10), @searchEDate,102) + ''')'

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources