Re: Need some help with simple search query
From: Drew (dlaing_at_NOswvtc.state.va.SPAMus)
Date: 07/14/04
- Next message: Anith Sen: "Re: Need some help with simple search query"
- Previous message: Brian W: "Re: surely, there's got to be a better way"
- In reply to: kirk1880: "RE: Need some help with simple search query"
- Next in thread: Drew: "Re: Need some help with simple search query"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 14 Jul 2004 12:18:27 -0400
Please forgive my ignorance, but that is Greek to me... I am using this for
an ASP page, where do I keep this? Do I create a stored procedure?
Thanks,
Drew
"kirk1880" <kirk1880@discussions.microsoft.com> wrote in message
news:93B8A3DA-FCB2-4CA8-BEE9-7A2CA78B229E@microsoft.com...
> 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: Anith Sen: "Re: Need some help with simple search query"
- Previous message: Brian W: "Re: surely, there's got to be a better way"
- In reply to: kirk1880: "RE: Need some help with simple search query"
- Next in thread: Drew: "Re: Need some help with simple search query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
