Muliple Search criterias - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Muliple Search criterias

Author

Topic

dovesdale
Starting Member

11 Posts

Posted - 2007-04-28 : 04:05:46

I have got an asp.net "search page" which searches records according to multiple crierias.say country,location,date,name etcc...
so i want to get records even if choose only one criteria,say country.
and if he choose location other than country then the search should be done with country and location
I tackled the problem by writting multiple if conditions but the problemis that my stored procedure is too long i need to write some 20 condition for the number of criterias i have

Can somebody help me with a better way

i am pating my stored procedure below
plz give me soem advise

CREATE PROC SPR_COM_CommunicationThreadList_FetchFORSearch

@HospitalID INT,
@USERID INT,
@ClientName Varchar(50),
@DateEntered Varchar(50),
@FromDate Varchar(50),
@ToDate Varchar(50),
@ClientTypeID INT,
@Keyword Varchar(100)

AS

IF @ClientName<>'' AND @DateEntered=''
BEGIN
SELECT COM_CD.ComClientFirstName + COM_CD.ComClientLastName AS [Client Name], '<A href="CommunicationThread.aspx?GID=' + CONVERT(VARCHAR,COM_CD.ClientID) +'&cTYPE='+CONVERT(VARCHAR,COM_ClientList.ClientTypeID)+ '" target=_blank>' +
'' + (COM_CommunicationThreadList.QuerryResponse) + '' + '</A>' As [Client Subject],
Convert(Varchar,COM_CommunicationThreadList.DateEntered) as [last added date]
FROM COM_CommunicationClientDetails COM_CD INNER JOIN
COM_CommunicationThreadList ON COM_CD.ClientID = COM_CommunicationThreadList.ClientID INNER JOIN
COM_ClientList ON COM_CommunicationThreadList.ClientID = COM_ClientList.ClientID AND
COM_ClientList.HospitalID = COM_CommunicationThreadList.HospitalID INNER JOIN
COM_ClientTypeList ON COM_ClientList.ClientTypeID = COM_ClientTypeList.ClientTypeID
WHERE (COM_CommunicationThreadList.ThreadID =
(SELECT MAX(threadID)
FROM COM_CommunicationThreadList INNER JOIN
COM_CommunicationClientDetails ON COM_CommunicationClientDetails.ClientID = COM_CommunicationThreadList.ClientID INNER JOIN
COM_ClientList ON COM_CommunicationThreadList.ClientID = COM_ClientList.ClientID
WHERE COM_CommunicationClientDetails.ClientID = COM_CD.ClientID)) AND (COM_CD.HospitalID = @HospitalID) AND
((COM_CD.ComClientFirstName + COM_CD.ComClientLastName)LIKE '%'+@ClientName+'%') AND (COM_CD.UserID = @USERID)

END

IF @DateEntered <> '' AND @ClientName=''

BEGIN
SELECT COM_CD.ComClientFirstName + COM_CD.ComClientLastName AS [Client Name], '<A href="CommunicationThread.aspx?GID=' + CONVERT(VARCHAR,COM_CD.ClientID) +'&cTYPE='+CONVERT(VARCHAR,COM_ClientList.ClientTypeID)+ '" target=_blank>' +
'' + (COM_CommunicationThreadList.QuerryResponse) + '' + '</A>' As [Client Subject],
Convert(Varchar,COM_CommunicationThreadList.DateEntered) as [last added date]
FROM COM_CommunicationClientDetails COM_CD INNER JOIN
COM_CommunicationThreadList ON COM_CD.ClientID = COM_CommunicationThreadList.ClientID INNER JOIN
COM_ClientList ON COM_CommunicationThreadList.ClientID = COM_ClientList.ClientID AND
COM_ClientList.HospitalID = COM_CommunicationThreadList.HospitalID INNER JOIN
COM_ClientTypeList ON COM_ClientList.ClientTypeID = COM_ClientTypeList.ClientTypeID
WHERE (COM_CommunicationThreadList.ThreadID =
(SELECT MAX(threadID)
FROM COM_CommunicationThreadList INNER JOIN
COM_CommunicationClientDetails ON COM_CommunicationClientDetails.ClientID = COM_CommunicationThreadList.ClientID INNER JOIN
COM_ClientList ON COM_CommunicationThreadList.ClientID = COM_ClientList.ClientID
WHERE COM_CommunicationClientDetails.ClientID = COM_CD.ClientID)) AND (COM_CD.HospitalID = @HospitalID) AND
(CONVERT(VARCHAR, COM_CommunicationThreadList.DateEntered, 106) = CONVERT(VARCHAR, CONVERT(DATETIME, @DateEntered), 106))
AND (COM_CD.UserID = @USERID)

END

IF @FromDate <> '' AND @ToDate <> ''

BEGIN
SELECT COM_CD.ComClientFirstName + COM_CD.ComClientLastName AS [Client Name], '<A href="CommunicationThread.aspx?GID=' + CONVERT(VARCHAR,COM_CD.ClientID) +'&cTYPE='+CONVERT(VARCHAR,COM_ClientList.ClientTypeID)+ '" target=_blank>' +
'' + (COM_CommunicationThreadList.QuerryResponse) + '' + '</A>' As [Client Subject],
Convert(Varchar,COM_CommunicationThreadList.DateEntered) as [last added date]
FROM COM_CommunicationClientDetails COM_CD INNER JOIN
COM_CommunicationThreadList ON COM_CD.ClientID = COM_CommunicationThreadList.ClientID INNER JOIN
COM_ClientList ON COM_CommunicationThreadList.ClientID = COM_ClientList.ClientID AND
COM_ClientList.HospitalID = COM_CommunicationThreadList.HospitalID INNER JOIN
COM_ClientTypeList ON COM_ClientList.ClientTypeID = COM_ClientTypeList.ClientTypeID
WHERE (COM_CommunicationThreadList.ThreadID =
(SELECT MAX(threadID)
FROM COM_CommunicationThreadList INNER JOIN
COM_CommunicationClientDetails ON COM_CommunicationClientDetails.ClientID = COM_CommunicationThreadList.ClientID INNER JOIN
COM_ClientList ON COM_CommunicationThreadList.ClientID = COM_ClientList.ClientID
WHERE COM_CommunicationClientDetails.ClientID = COM_CD.ClientID)) AND (COM_CD.HospitalID = @HospitalID) AND
(CONVERT(VARCHAR, COM_CommunicationThreadList.DateEntered, 106) Between CONVERT(VARCHAR, CONVERT(DATETIME, @FromDate), 106) AND CONVERT(VARCHAR, CONVERT(DATETIME, @ToDate), 106))
AND (COM_CD.UserID = @USERID)

END

IF @ClientTypeID <> 0

BEGIN

SELECT COM_CD.ComClientFirstName + COM_CD.ComClientLastName AS [Client Name], '<A href="CommunicationThread.aspx?GID=' + CONVERT(VARCHAR,COM_CD.ClientID) +'&cTYPE='+CONVERT(VARCHAR,COM_ClientList.ClientTypeID)+ '" target=_blank>' +
'' + (COM_CommunicationThreadList.QuerryResponse) + '' + '</A>' As [Client Subject],
Convert(Varchar,COM_CommunicationThreadList.DateEntered) as [last added date]
FROM COM_CommunicationClientDetails COM_CD INNER JOIN
COM_CommunicationThreadList ON COM_CD.ClientID = COM_CommunicationThreadList.ClientID INNER JOIN
COM_ClientList ON COM_CommunicationThreadList.ClientID = COM_ClientList.ClientID AND
COM_ClientList.HospitalID = COM_CommunicationThreadList.HospitalID INNER JOIN
COM_ClientTypeList ON COM_ClientList.ClientTypeID = COM_ClientTypeList.ClientTypeID
WHERE (COM_CommunicationThreadList.ThreadID =
(SELECT MAX(threadID)
FROM COM_CommunicationThreadList INNER JOIN
COM_CommunicationClientDetails ON COM_CommunicationClientDetails.ClientID = COM_CommunicationThreadList.ClientID INNER JOIN
COM_ClientList ON COM_CommunicationThreadList.ClientID = COM_ClientList.ClientID
WHERE COM_CommunicationClientDetails.ClientID = COM_CD.ClientID)) AND (COM_CD.HospitalID = @HospitalID) AND
COM_ClientTypeList.ClientTypeID = @ClientTypeID
AND (COM_CD.UserID = @USERID)

END

IF @Keyword <> ''

BEGIN

SELECT COM_CD.ComClientFirstName + COM_CD.ComClientLastName AS [Client Name], '<A href="CommunicationThread.aspx?GID=' + CONVERT(VARCHAR,COM_CD.ClientID) +'&cTYPE='+CONVERT(VARCHAR,COM_ClientList.ClientTypeID)+ '" target=_blank>' +
'' + (COM_CommunicationThreadList.QuerryResponse) + '' + '</A>' As [Client Subject],
Convert(Varchar,COM_CommunicationThreadList.DateEntered) as [last added date]
FROM COM_CommunicationClientDetails COM_CD INNER JOIN
COM_CommunicationThreadList ON COM_CD.ClientID = COM_CommunicationThreadList.ClientID INNER JOIN
COM_ClientList ON COM_CommunicationThreadList.ClientID = COM_ClientList.ClientID AND
COM_ClientList.HospitalID = COM_CommunicationThreadList.HospitalID INNER JOIN
COM_ClientTypeList ON COM_ClientList.ClientTypeID = COM_ClientTypeList.ClientTypeID
WHERE (COM_CommunicationThreadList.ThreadID =
(SELECT MAX(threadID)
FROM COM_CommunicationThreadList INNER JOIN
COM_CommunicationClientDetails ON COM_CommunicationClientDetails.ClientID = COM_CommunicationThreadList.ClientID INNER JOIN
COM_ClientList ON COM_CommunicationThreadList.ClientID = COM_ClientList.ClientID
WHERE COM_CommunicationClientDetails.ClientID = COM_CD.ClientID)) AND (COM_CD.HospitalID = @HospitalID) AND
((COM_CommunicationThreadList.QuerryResponse)LIKE '%'+@Keyword+'%')
AND (COM_CD.UserID = @USERID)

END
GO

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-28 : 04:10:26

give this a read for different strategies on dynamic search criteria:

http://www.sommarskog.se/dyn-search.html

www.elsasoft.org

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-28 : 21:50:43

This is a case where dynamic SQL is appropriate, and probably more efficient than direct SQL.

e4 d5 xd5 Nf6

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-28 : 22:48:25

unrelated point: others may disagree, but I don't like putting presentation markup in the database. this proc is returning data already marked up in HTML. it means that it's only useful for clients that can render HTML, and further if you decide you want some other way of marking up the data in the future, you have to change the database layer. finally, it means different HTML clients can't differ in the way they present the data.

presentation details belong in the presentation layer. database should provide data only. to mix them leads to a brittle UI design in my experience.

www.elsasoft.org

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-28 : 23:41:40

Two other points:

1) from a web design perspective, naming your css class "green" completely negates the entire point of CSS. And,as mentioned, putting HTML in your database code is a big no-no. follow jezemine's advice.

2) never concatenate data like that into a sql statement. even dynamic sql can still use parameters; you should use sp_executeSQL and assign parameter values to your SQL, don't convert everything to a big string and just execute it. Read books on line (SQL Server help) for info on using sp_executeSQL with parameters.

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-28 : 23:44:43

Another point:

3) You should also always use proper data types for your parameters; If those various date parameters should be dates, do NOT use VARCHAR as your datatype. This applies to your database tables as well, of course.

- Jeff
http://weblogs.sqlteam.com/JeffS

dovesdale
Starting Member

11 Posts

Posted - 2007-05-01 : 04:34:48

thanks
it did work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources