Dyanamic SQL...HELP!!!! - SQL Server Forums

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

Dyanamic SQL...HELP!!!!

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-12 : 11:45:35

Hello Again,

Here is what I did for my normal search,and this is working fine.
But I need to do similar thing for advance search,where table name will be entered by the user.table names will be in dropdownlist.
I know I have to use dynamic sql,but somehow I am not being able to implement this.

Here is what I did for normal search,....

USE [charlotte_production]
GO
/****** Object: StoredProcedure [dbo].[sp_SystemSearch] Script Date: 08/12/2008 11:37:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SystemSearch]

@text as varchar(300)
AS

BEGIN
SET NOCOUNT ON;
declare @srh as nvarchar(50)
SET @srh = 'FORMSOF(INFLECTIONAL,'+@text+')'

SELECT s.SystemElementID,DocumentID,SETitleText as "Title",SESummaryText as "SearchElement"
FROM SystemElement s,TOCMap tp,TableOfContents tc
where SESummaryText LIKE '%'+@text+'%' AND s.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID
Union all
Select s.SystemElementID,DocumentID, SETitleText as "Title",ElementText as "SearchElement"
FROM TextElement t,SystemElement s,TOCMap tp,TableOfContents tc
Where t.SystemElementID=s.SystemElementID AND Freetext(ElementText,@srh) AND s.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID
UNION ALL

Select s.SystemElementID,DocumentID, SETitleText as "Title",VideoMimeType as "SearchElement"
FROM VideoElement v,SystemElement s,TOCMap tp,TableOfContents tc
Where v.SystemElementID=s.SystemElementID AND Freetext(VideoMimeType,@srh) AND s.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID

UNION ALL

Select s.SystemElementID,DocumentID, SETitleText as "Title",d.MetatagText as "SearchElement"
From SystemElement s,MetaTag t,MetaTagDictionary d,TOCMap tp,TableOfContents tc
Where t.SystemElementID=s.SystemElementID AND d.MetatagText=t.MetatagText AND Freetext(d.MetaTagText,@srh) AND s.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID
END

Please help!!!!

Thank You.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 12:23:58

which table will be selected by user? or will you give flexibility to select all table? will they all be having same structure? or how will you determine what fields to be joined?

X002548
Not Just a Number

15586 Posts

Posted - 2008-08-12 : 12:45:19

That's not nice

SESummaryText LIKE '%'+@text+'%'

How big is your table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-12 : 12:49:57

okay, all the tables will not be selected at a time.
User will select only one table from dropdownlist at a time,

Here are the table structures

table---TextElement
columns--SystemElementID PK
--ElementText

Table--VideoElement
columns--SystemElementID PK
--VideoMimeType

Table MetaTag
Columns--MetaTagID PK
--SystemElementID FK
-- MetaTagText FK

Table MetaTagDictionary
Columns--MetaTagText PK

Table--SystemElement
Columns SystemElementID PK
SETitleText
SESummaryText

Table ---TOCMap
Columns---TOCMapID PK
SystemElementID FK
TableOFContentsID FK

Table ---TableOfContents
Columns--TableOFContentID
DocumentID................Here lies the documentID

I need to search ElementText,VideoMimeType,SESummaryText,MetaTagText

And I need to display
SystemElementID,DocumentID,SETitleText, and Search element

Thank You

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-12 : 12:55:03

I tried doing something like this...This is not what I exactly want but I tried a sample
USE [charlotte_production]
GO
/****** Object: StoredProcedure [dbo].[sp_AdvanceSearch1] Script Date: 08/12/2008 09:33:00 ******/

ALTER PROCEDURE [dbo].[sp_AdvanceSearch1]
(
@Text VARCHAR(300),
@Text1 VARCHAR(300),
@Text2 VARCHAR(300),
@tblname VARCHAR(300)
)

AS

SET NOCOUNT ON

-- Show the results
--DECLARE @Params VARCHAR(1000)
Declare @SQL varchar(8000)
Declare @Element varchar(200)
--Declare @Params varchar(200)

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

Set @Element = case @tblname
When 'SystemElement' then ' SESummaryText '
When 'TextElement t' then 'ElementText'
When 'VideoElement' then 'VideoMimeType'
When 'MetaTag' then 'MetatagText'
END -- Ends the CASE statement

set @SQl = 'Select s.SystemElementID,SETitleText'
+ @Element + ' as [SearchElement]'

SET @SQL = @SQL + 'FROM SystemElement Inner Join ' + @tblname + 't ON
WHERE s.SystemElementID=t.SystemElementID AND FREETEXT(' + @Element + ',''' + @Text + ''') AND FREETEXT(' + @Element + ',''' + @Text1 + ''') OR FREETEXT(' + @Element + ',''' + @Text2 + ''') '

--Print (@SQL)
EXEC (@SQL)

But I am not getting any result.its giving me zero result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 13:07:39

So your SystemElement table will be same through out?

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-12 : 13:09:46

Sorry but I didnt get your question.
Actually SystemElement is the table where all other tables like TextElement,VideoElement ,TOCMap MetaTag are linked to it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 13:15:18

quote:
Originally posted by bluestar

Sorry but I didnt get your question.
Actually SystemElement is the table where all other tables like TextElement,VideoElement ,TOCMap MetaTag are linked to it.

Ok. i understand that. What i was asking was whether its one among other tables that user will be selecting and they will be joined to this table always?

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-12 : 13:20:49

yes this will be one among the tables that user will be selecting.
But also if user select TextElement or any other table,then also by default Systemelement table will have to be selected in the From clause.
SO as to match the SystemElementID.

like what I did for normal search

Select s.SystemElementID,DocumentID, SETitleText as "Title",ElementText as "SearchElement"
FROM TextElement t,SystemElement s,TOCMap tp,TableOfContents tc
Where t.SystemElementID=s.SystemElementID AND Freetext(ElementText,@srh) AND s.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID

Hope you understood what I am trying to say.
Thank You

bluestar
Posting Yak Master

133 Posts

Posted - 2008-08-12 : 15:08:18

okay I came up with something where my two of the tables i.e. TextElement and VideoElement is working.

BUT the problem is when it comes to metatagdictionary ,the metatagdictionary table does not have Column SystemElementID, and is connected to table SystemElement via Table MetaTag.

So in this case can you please help me how should I modify my current stored procedure.

Can I just write seperate sql and make union all????Not sure !!!

USE [charlotte_production]
GO
/****** Object: StoredProcedure [dbo].[sp_AdvanceSearch1] Script Date: 08/12/2008 09:33:00 ******/

ALTER PROCEDURE [dbo].[sp_AdvanceSearch1]
(
@Text VARCHAR(300),
@Text1 VARCHAR(300),
@Text2 VARCHAR(300),
@tblname VARCHAR(300)
)

AS

SET NOCOUNT ON

-- Show the results
--DECLARE @Params VARCHAR(1000)
Declare @SQL varchar(8000)
Declare @Element varchar(200)
--Declare @Params varchar(200)

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

Set @Element = case @tblname
When 'SystemElement' then 'SESummaryText'
When 'TextElement' then 'ElementText'
When 'VideoElement' then 'VideoMimeType'
When 'MetaTagDictionary' then 'MetatagText'
END -- Ends the CASE statement

set @SQl = 'Select s.SystemElementID, TOC.DocumentID, SETitleText, '
+ @Element + ' as [SearchElement]'

SET @SQL = @SQL + 'FROM SystemElement s Inner Join ' +
@tblname + ' t On s.SystemElementID=t.SystemElementID Inner Join
TOCMap on s.SystemElementID=TOCMap.SystemElementID Inner Join
TableOfContents TOC on TOCMap.TableOfContentsID = TOC.TableOfContentsID
WHERE FREETEXT(' + @Element + ',''' + @Text + ''') OR FREETEXT(' + @Element + ',''' + @Text1 + ''') OR FREETEXT(' + @Element + ',''' + @Text2 + ''') '

--Print (@SQL)
EXEC (@SQL)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-12 : 16:41:07

Hi bluestar,

I think you are in danger of over-complicating this.

You have a stored procedure where the user can select a drop down table (1 of 3) - and these tables all exist at run time?

Why not just do an IF statement in your stored proc and then select from that particular table in a static way? Lots of benefits, no disadvantages ( as long as the table names aren't dynamic which you say they are not )

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources