a syntax error

Question

Post reply

a syntax error

nazaninahmady_sh

Default port

Points: 1465
More actions
July 5, 2010 at 5:15 am

#223488

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SearchCourse]
@VarCourse_Id int,
@VarCourse_Name nvarchar(50),
@VarCourse_Institue nvarchar(50),
@VarEdarekol_Id int,
@VarVahed_Id int
AS
BEGIN
Select * from Course
where
(Course_Id = Case when ((@VarCourse_Id=0) or (@VarCourse_Id is null))
Then Course_Id Else @VarCourse_Id end)and
(Course_Name=Case when ((@VarCourse_Name='') or (@VarCourse_Name is null))
Then Course_Name Else (freetext(Course_Name,@VarCourse_Name)) end)and
(Course_Institue=Case when ((@VarCourse_Institue='') or (@VarCourse_Institue is null) )
Then Course_Institue Else @VarCourse_Institue end)and
(Edarekol_Id=case when ((@VarEdarekol_Id=0) or (@VarEdarekol_Id is null))
then Edarekol_Id else @VarEdarekol_Id end)and
(Vahed_Id= case when ((@VarVahed_Id=0) or (@VarVahed_Id is null))
then Vahed_Id else @VarVahed_Id end)
End
it is written that: Incorrect syntax near the keyword 'freetext'.
would u please help me?

Viewing 10 posts - 1 through 9 (of 9 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Assuming 'freetext' is a UDF then you need to qualify it with the schema name to which it belongs, typically like this:

(Course_Name = Case when ((@VarCourse_Name='') or (@VarCourse_Name is null))

Then Course_Name Else (dbo.freetext(Course_Name,@VarCourse_Name)) end)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nazaninahmady_sh Default port Points: 1465 More actions · Answer 2

thank you for your notification but again i have that message:

Incorrect syntax near the keyword 'freetext'.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

nazaninahmady_sh (7/5/2010)
thank you for your notification but again i have that message:
Incorrect syntax near the keyword 'freetext'.

Sorry, my mistake. This is a component of full-text searching. Is full-text search enabled? From BOL: "x Is the name of the column that has been registered for full-text searching".

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nazaninahmady_sh Default port Points: 1465 More actions · Answer 4

nazaninahmady_sh

Default port

Points: 1465

July 5, 2010 at 5:45 am

#1188743

what is the udf?

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

FREETEXT is not UDF, it is in-build predicate. It cannot be used in such context.

You have used it similar to the following:

...

CASE WHEN ColA>ColB THEN ColC ELSE ColD>ColE

...

While it shoud be used like:

...

WHERE FREETEXT(ColA,'some text')

Read the BoL:

http://msdn.microsoft.com/en-us/library/ms176078(SQL.90).aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

nazaninahmady_sh Default port Points: 1465 More actions · Answer 6

ok, Course_Name if column name , so i wroteit correctly:

Else (freetext(Course_Name,@VarCourse_Name))

but there is syntax error

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

nazaninahmady_sh (7/5/2010)
ok, Course_Name if column name , so i wroteit correctly:
Else (freetext(Course_Name,@VarCourse_Name))
but there is syntax error

As Eugene points out, this won't work because you're attempting to compare

Course_Name with (freetext(Course_Name,@VarCourse_Name)). The correct syntax is WHERE freetext(Course_Name,@VarCourse_Name).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nazaninahmady_sh Default port Points: 1465 More actions · Answer 8

thank you i understand what are you saying but there is another problem, and that is: maybe maybe operator send null or ' ' , so i have to check , and i have to check ir with case like what ever i wrote it before so the problem is occured

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

You could split it into two possible options like this:

IF @VarCourse_Name IS NULL

BEGIN

SELECT *

FROM Course

WHERE freetext(Course_Name, @VarCourse_Name)

AND Course_Id = COALESCE(Course_Id, NULLIF(@VarCourse_Id, 0))

AND ...

END

ELSE

BEGIN

SELECT *

FROM Course

WHERE 1 = 1

AND Course_Id = COALESCE(Course_Id, NULLIF(@VarCourse_Id, 0))

AND ...

END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden