RE: Need some help with simple search query
From: kirk1880 (kirk1880_at_discussions.microsoft.com)
Date: 07/14/04
- Next message: Andrew J. Kelly: "Re: help - transaction control"
- Previous message: SKG: "Re: Could not find a default MAPI profile"
- In reply to: Drew: "Need some help with simple search query"
- Next in thread: Drew: "Re: Need some help with simple search query"
- Reply: Drew: "Re: Need some help with simple search query"
- Reply: Drew: "Re: Need some help with simple search query"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 14 Jul 2004 09:06:04 -0700
There are two ways you can do this:
1st and the perfered way is to write out all the tedious logic
IF @v_variable1 IS NOT NULL
BEGIN -- 1 is not null
IF @v_variable2 IS NOT NULL
BEGIN -- 2 is not null
IF @v_variable3 IS NOT NULL
BEGIN -- 3 is not null
SELECT whatever
FROM whatever
WHERE column1 = @v_variable1
AND column2 = @v_variable2
AND column3 = @v_variable3
END -- 3 is not null
ELSE
BEGIN -- 3 is null
SELECT whatever
FROM whatever
WHERE column1 = @v_variable1
AND column2 = @v_variable2
END -- 3 is null
END -- 2 is not null
ELSE
BEGIN -- 2 is null
SELECT whatever
FROM whatever
WHERE column1 = @v_variable1
END -- 2 is null
END -- 1 is not null
IF @v_variable1 IS NULL AND @v_variable2 IS NOT NULL
BEGIN
and so forth and so forth.....
2nd way (and I would only recomend this if the query is going against a small result set)
DECLARE @v_select NVARCHAR(2000),
@v_where NVARCHAR(2000)
SELECT @v_select =
'SELECT Whatever ' + CHAR(13) +
' FROM whatever',
@v_where = ''
IF @v_variable1 IS NOT NULL
BEGIN
SELECT @v_where = @v_where + 'AND col1 = @v_variable1' + CHAR(13) + ' '
END
IF @v_variable2 IS NOT NULL
BEGIN
SELECT @v_where = @v_where + 'AND col1 = @v_variable2' + CHAR(13) + ' '
END
and so forth and so forth until you get through all the variables, then
SELECT @v_select = @v_select + STUFF(@v_where, 1, 3, 'WHERE')
EXEC dbo.sp_executesql @v_select
"Drew" wrote:
> I have the following query,
>
> SELECT CA.RegNo, CA.ApptDoctor, CA.ApptDate, CA.ApptTime, CA.ApptNotes,
> AC.ApptCatName, C.CliLName, C.CliMM, C.CliFName
> FROM CliDrAppts.dbo.tblCliAppts CA INNER JOIN CliCore.dbo.tblClients C ON
> CA.RegNo = C.RegNo INNER JOIN tblCliApptCat AC ON CA.ApptCatID =
> AC.ApptCatID
>
> This joins all the tables I need and now I need to get some WHERE clauses in
> there. I have a page that has 4 form elements on it, a dropdown with all
> the months in it, a dropdown with years in it (from 2004 to 2020), a dynamic
> dropdown with Resident's names and a dynamic dropdown with Appointment
> Categories. I am having problems with this because if you omit one of these
> search criteria, then it doesn't return any results. Here is my new query,
>
> SELECT CA.RegNo, CA.ApptDoctor, CA.ApptDate, CA.ApptTime, CA.ApptNotes,
> AC.ApptCatName, C.CliLName, C.CliMM, C.CliFName
> FROM CliDrAppts.dbo.tblCliAppts CA INNER JOIN CliCore.dbo.tblClients C ON
> CA.RegNo = C.RegNo INNER JOIN tblCliApptCat AC ON CA.ApptCatID =
> AC.ApptCatID
> WHERE LEFT(ApptDate,2) = var1 AND RIGHT(ApptDate,4) = var2 AND CA.ApptCatID
> = var3 AND CA.RegNo = var4
>
> var1 = request.form("Month")
> var2= request.form("Year")
> var3 = request.form("Category")
> var4 = request.form("Resident")
>
> How can I make this so that if the user wants to search for records
> pertaining to just the Resident, or if the user wants to search for records
> in a specific month. I thought about using something like this,
>
> If request.form("Month") <> "" Then
> sql = blah,blah
> Else
> sql = blah,blah
>
> But there are too many combinations, I just think there should be an easier
> way to do this, but the OR in the SQL Statement doesn't work either, then it
> returns too many records.
>
> Thanks,
> Drew
>
>
>
- Next message: Andrew J. Kelly: "Re: help - transaction control"
- Previous message: SKG: "Re: Could not find a default MAPI profile"
- In reply to: Drew: "Need some help with simple search query"
- Next in thread: Drew: "Re: Need some help with simple search query"
- Reply: Drew: "Re: Need some help with simple search query"
- Reply: Drew: "Re: Need some help with simple search query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
