Stored procedure error,,,please help!!!!!

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

General SQL Server Forums

New to SQL Server Programming

Stored procedure error,,,please help!!!!!

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2008-07-24 : 10:26:58

hello

I am trying to do advance search on my database on multiple tables.
The user will enter the search term and select whether they want ALL
words or ANY one words.I am using SQL server 2005.
AND here is my code..

@text as nchar(300) //....this will be textbox1,which will search all words
@text1 as nchar(300)//....textbox2 either of word
@text2 as nchar(300)// textbox3

AS
BEGIN
SET NOCOUNT ON;

SELECT ElementText
FROM TextElement
where freetext(ElementText,@text AND @text1 OR @text2)
union all
SELECT VideoMimeType
from VideoElement
where freetext(VideoMimeType,@text AND @text1 OR @text2)
union all
SELECT SESummaryText
FROM SystemElement
where freetext(SESummaryText,@text AND @text1 OR @text2)

END
GO

AND THIS IS THE ERROR I AM GETTING

Msg 102, Level 15, State 1, Procedure sp_AdvanceSearch, Line 5
Incorrect syntax near '@text1'.
Msg 137, Level 15, State 2, Procedure sp_AdvanceSearch, Line 14
Must declare the scalar variable "@text".
Msg 137, Level 15, State 2, Procedure sp_AdvanceSearch, Line 18
Must declare the scalar variable "@text".
Msg 137, Level 15, State 2, Procedure sp_AdvanceSearch, Line 22
Must declare the scalar variable "@text".

Please help,actually i am new to stored procedure and have limited idea on how solve this error.

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 10:33:51

You cant use variables directly inside freetext. you need to execute the above code dynamically. something like

declare @Sql varchar(8000)
SET @Sql='SELECT ElementText 
FROM TextElement 
where freetext(ElementText,'+@text+' AND '+ @text1 +' OR '+ @text2 +')
union all 
SELECT VideoMimeType 
from VideoElement 
where freetext(VideoMimeType,'@text' AND '+ @text1 + ' OR '+ @text2 +')
union all
SELECT SESummaryText
FROM SystemElement
where freetext(SESummaryText,'@text' AND '+ @text1 + ' OR '+ @text2 + ')'

EXEC(@Sql)

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-24 : 10:36:52

Use the like key word in u'r query.

select col1 from table1 where col1 like 'data%'

bluestar
Posting Yak Master

133 Posts

Posted - 2008-07-24 : 10:39:47

thanks for reply,but now its giving me this error

Msg 156, Level 15, State 1, Procedure sp_AdvanceSearch, Line 4
Incorrect syntax near the keyword 'declare'.
Msg 137, Level 15, State 2, Procedure sp_AdvanceSearch, Line 7
Must declare the scalar variable "@text".

I am new to stored procedure,would appreciate your help.

Thank You

bluestar
Posting Yak Master

133 Posts

Posted - 2008-07-24 : 10:43:51

here is my modified code

CREATE PROCEDURE dbo.sp_AdvanceSearch
-- Add the parameters for the stored procedure here
@text as nchar(300)
@text1 as nchar(300)
@text2 as nchar(300)

As begin
SET NOCOUNT ON
declare @Sql varchar(8000)
SET @Sql='SELECT ElementText
FROM TextElement
where freetext(ElementText,'+@text+' AND '+ @text1 +' OR '+ @text2 +')
union all
SELECT VideoMimeType
from VideoElement
where freetext(VideoMimeType,'@text' AND '+ @text1 + ' OR '+ @text2 +')
union all
SELECT SESummaryText
FROM SystemElement
where freetext(SESummaryText,'@text' AND '+ @text1 + ' OR '+ @text2 + ')'

EXEC(@Sql)
End

ERROR::::::::::

Msg 102, Level 15, State 1, Procedure sp_AdvanceSearch, Line 4
Incorrect syntax near '@text1'.
Msg 137, Level 15, State 2, Procedure sp_AdvanceSearch, Line 12
Must declare the scalar variable "@text".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 10:44:44

quote:
Originally posted by bluestar

thanks for reply,but now its giving me this error

Msg 156, Level 15, State 1, Procedure sp_AdvanceSearch, Line 4
Incorrect syntax near the keyword 'declare'.
Msg 137, Level 15, State 2, Procedure sp_AdvanceSearch, Line 7
Must declare the scalar variable "@text".

I am new to stored procedure,would appreciate your help.

Thank You

missed a couple of +'s

SET @Sql='SELECT ElementText 
FROM TextElement 
where freetext(ElementText,'+@text+' AND '+ @text1 +' OR '+ @text2 +')
union all 
SELECT VideoMimeType 
from VideoElement 
where freetext(VideoMimeType,'+@text+' AND '+ @text1 + ' OR '+ @text2 +')
union all
SELECT SESummaryText
FROM SystemElement
where freetext(SESummaryText,'+@text+' AND '+ @text1 + ' OR '+ @text2 + ')'

EXEC(@Sql)

bluestar
Posting Yak Master

133 Posts

Posted - 2008-07-24 : 10:48:57

yes I corrected that,and add '+'

but problem persist

Msg 102, Level 15, State 1, Procedure sp_AdvanceSearch, Line 9
Incorrect syntax near '@text1'.
Msg 137, Level 15, State 2, Procedure sp_AdvanceSearch, Line 17
Must declare the scalar variable "@text".

PLEASE HELP....
THANK YOU

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 11:41:13

All parameters (excluding last) must have a trailing comma and no "as"...

CREATE PROCEDURE dbo.sp_AdvanceSearch
(
-- Add the parameters for the stored procedure here
@text nchar(300) ,
@text1 nchar(300) ,
@text2 nchar(300)
)
As

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 11:49:04

Why dynamic SQL at all?

CREATE PROCEDURE dbo.sp_AdvanceSearch
(
	@Text VARCHAR(300),
	@Text1 VARCHAR(300),
	@Text2 VARCHAR(300)
)
AS

SET NOCOUNT OFF

DECLARE @Params VARCHAR(1000)

SET	@Params = QUOTENAME(@Text, '''') + ' AND ' + QUOTENAME(@Text1, '''') + ' OR ' + QUOTENAME(@Text2, '''')

-- Show the results
SELECT	ElementText 
FROM	TextElement 
WHERE	FREETEXT(ElementText, @Params)

UNION ALL 

SELECT	VideoMimeType 
FROM	VideoElement 
WHERE	FREETEXT(VideoMimeType, @Params)

UNION ALL

SELECT	SESummaryText
FROM	SystemElement
WHERE	FREETEXT(SESummaryText, @Params)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 11:51:54

And if you must keep dynamic SQL, try this

CREATE PROCEDURE dbo.sp_AdvanceSearch
(
	@Text VARCHAR(300),
	@Text1 VARCHAR(300),
	@Text2 VARCHAR(300)
)
AS

SET NOCOUNT ON

DECLARE	@SQL VARCHAR(8000)

SET	@Sql = '
SELECT	ElementText 
FROM	TextElement 
WHERE	FREETEXT(ElementText, ' + QUOTENAME(@Text, '''') + ' AND ' + QUOTENAME(@Text1, '''') + ' OR ' + QUOTENAME(@Text2, '''') + ')

UNION ALL 

SELECT	VideoMimeType 
FROM	VideoElement 
WHERE	FREETEXT(VideoMimeType, , ' + QUOTENAME(@Text, '''') + ' AND ' + QUOTENAME(@Text1, '''') + ' OR ' + QUOTENAME(@Text2, '''') + ')

UNION ALL

SELECT	SESummaryText
FROM	SystemElement
WHERE	FREETEXT(SESummaryText, ' + QUOTENAME(@Text, '''') + ' AND ' + QUOTENAME(@Text1, '''') + ' OR ' + QUOTENAME(@Text2, '''') + ')
'

EXEC	(@SQL)

E 12°55'05.25"
N 56°04'39.16"

bluestar
Posting Yak Master

133 Posts

Posted - 2008-07-24 : 14:04:07

Millions of thanks everybody!!!!
Because I am a starter in this field ,I am always with full of quires,will get back here again soon!!
GOD BLESS!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources