quote:

---

Originally posted by bluestar

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.And""" ALSO USER WILL SELECT FROM DROPDOWNLIST FROM WHICH SPECIFIC TABLE HE WANT TO SEARCH"""".And I dont how to implement dropdownlist in FROM clause in the sql query, and also I think here Union all is not correct as user is searching from specific table.please help me how to write correct stored procedure for this.

I am using SQL server 2005.
AND here is my code..
ALTER PROCEDURE [dbo].[sp_ASearch]
(
@Text VARCHAR(300),
@Text1 VARCHAR(300),
@Text2 VARCHAR(300)
)
AS

SET NOCOUNT OFF

DECLARE @Params VARCHAR(1000)
DECLARE @Drop VARCHAR(100)

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

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

UNION ALL

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

UNION ALL

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

UNION ALL

SELECT MetaTagText
FROM @Drop
Where FREETEXT(MetaTagText,@Params)

ERROR
Msg 1087, Level 15, State 2, Procedure sp_ASearch, Line 21
Must declare the table variable "@Drop".
Msg 1087, Level 15, State 2, Procedure sp_ASearch, Line 27
Must declare the table variable "@Drop".
Msg 1087, Level 15, State 2, Procedure sp_ASearch, Line 33
Must declare the table variable "@Drop".
Msg 1087, Level 15, State 2, Procedure sp_ASearch, Line 39
Must declare the table variable "@Drop".

---

quote:

---

Originally posted by bluestar

thanks for the reply

Ok so you mean something like this...
ALTER PROCEDURE [dbo].[sp_ASearch]
(
@Text VARCHAR(300),
@Text1 VARCHAR(300),
@Text2 VARCHAR(300)
)
AS

SET NOCOUNT OFF

DECLARE @Params VARCHAR(1000)
DECLARE @Drop Table
{
(
VideoElement,
SystemElement,
TextElement,
MetaTagDictionary
)
}

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

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

UNION ALL

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

UNION ALL

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

UNION ALL

SELECT MetaTagText
FROM @Drop
Where FREETEXT(MetaTagText,@Params)


I am getting error

Msg 102, Level 15, State 1, Procedure sp_ASearch, Line 16
Incorrect syntax near '{'.

---

quote:

---

Originally posted by bluestar

sorry sorry yes it was my mistake......

I corrected it, but then its

Msg 173, Level 15, State 1, Procedure sp_ASearch, Line 18
The definition for column 'VideoElement' must include a data type.

The videoElement,TextElement...are tables,it cannot have datatype,

so what to do now,

Please do reply

---

quote:

---

Originally posted by bluestar


well I am doing advance search,where there will be textbox in which user will enter text to search and a dropdownlist,which will contain different tables,user will select from dropdownlist that from which particular table they want to search the text. And a button,which after submit will display data in datagrid.

Thank You

---

quote:

---

Originally posted by bluestar

this search is performed on a document management system, which will have different elements,like text element,binary element,Video element.
and all these are different tables, which are link to one table called System element which will contain ID,Title text, summary and likes.
And in the advance search I am told to give user facility to do specific search,whether they want to search video,text or they want to search from metatagdicionary,or systemelemnt summary.

This what I have to do,please try to explain me with example,about the if else thing you are talking about.
Hope I am being able to explain you properly ,if not please let me know...

Thank You

---

quote:

---

Originally posted by bluestar

SURE here are the structure of different tables
1.table SystemElement
columns....
SystemElementID int
SEVersion int
SETitleText varchar(500)
SESummaryText varchar(500)

2. table TextElement
columns
SystemElementID int pk,fk
ElementText ntext
ElementURLText varchar(50)

3. table VideoElement
columns
SystemElementID int pk,fk
VideoMimeType varchar(300)
VideoLocationURL varchar(50)

4 table MetatagDictionary
MetatagText varchar(400) pk
Createdby(int)
date


thanks

---

quote:

---

Originally posted by bluestar

thank you visakh16,

but sorry,I am not being able to understand your query,
1st of all do we require union all here,because here I am searching from one specific table,

I am feeling that your query is correct because inner join is required,
but if will be great if you can explain me bit why are you doing this
SELECT SystemElementID,VideoMimeType AS StrValue,VideoLocationURL AS LocValue,'Video' AS TypeValue

why SystemElementID,VideoMimeType AS StrValue
and VideoLocationURL AS LocValue,'Video' AS TypeValue
and also what is 'Video' here for.

I am sorry if my question is stupid,but I dont have enough experience in stored procedure.

please do reply

Thank You

---