Creating a Search Stored Procedure  
Author Message
SherryPhillips





PostPosted: Thu Jul 01 15:56:28 CDT 2004 Top

SQL Server Developer >> Creating a Search Stored Procedure

I have the following table structure

Table: Person
Columns:
ID: int identity
FirstName: varchar(50)
LastName: varchar(50)
BirthDate: datetime

I would like to create a GetPerson stored procedure that takes any combination of column values as parameters and searches based on them. If a particular column wasn't included then it's treated as "all" for that column. How would I build the query within the proc?

Create Procedure GetPerson
(




)
AS
????
GO





I'm trying to keep from having a bunch of procs to maintain.

Thanks in advance
Chris

SQL Server129  
 
 
Zach





PostPosted: Thu Jul 01 15:56:28 CDT 2004 Top

SQL Server Developer >> Creating a Search Stored Procedure
>
> I would like to create a GetPerson stored procedure that takes any
combination of column values as parameters and searches based on them.
If a particular column wasn't included then it's treated as "all" for
that column. How would I build the query within the proc?
>


The general way of doing this (although doesn't make good use of
indexes) is like this:

Create Procedure GetPerson




AS

SELECT <your list>
FROM Person
WHERE




GO

Zach
 
 
news





PostPosted: Thu Jul 01 15:57:38 CDT 2004 Top

SQL Server Developer >> Creating a Search Stored Procedure Try something like

SELECT <column_list>
FROM Person


...



> I have the following table structure
>
> Table: Person
> Columns:
> ID: int identity
> FirstName: varchar(50)
> LastName: varchar(50)
> BirthDate: datetime
>
> I would like to create a GetPerson stored procedure that takes any
combination of column values as parameters and searches based on them. If a
particular column wasn't included then it's treated as "all" for that
column. How would I build the query within the proc?
>
> Create Procedure GetPerson
> (




> )
> AS
> ????
> GO
>
> If a caller called the procedure with no parameters then all person

"Jones" then all person records with the last name "Jones" would be returned
(Exact match, not considering LIKE at this time)
>

@lastNameToSearch then all person records with John Smith would be returned.
etc.
>
> I'm trying to keep from having a bunch of procs to maintain.
>
> Thanks in advance
> Chris


 
 
John





PostPosted: Thu Jul 01 16:14:40 CDT 2004 Top

SQL Server Developer >> Creating a Search Stored Procedure Hi

You could use the following type of construct

Create Procedure GetPerson
(




)
AS
SELECT
ID,
FirstName,
LastName,
BirthDate
FROM Person






John



> I have the following table structure
>
> Table: Person
> Columns:
> ID: int identity
> FirstName: varchar(50)
> LastName: varchar(50)
> BirthDate: datetime
>
> I would like to create a GetPerson stored procedure that takes any
combination of column values as parameters and searches based on them. If a
particular column wasn't included then it's treated as "all" for that
column. How would I build the query within the proc?
>
> Create Procedure GetPerson
> (




> )
> AS
> ????
> GO
>
> If a caller called the procedure with no parameters then all person

"Jones" then all person records with the last name "Jones" would be returned
(Exact match, not considering LIKE at this time)
>

@lastNameToSearch then all person records with John Smith would be returned.
etc.
>
> I'm trying to keep from having a bunch of procs to maintain.
>
> Thanks in advance
> Chris


 
 
ChrisMartin





PostPosted: Thu Jul 01 16:13:01 CDT 2004 Top

SQL Server Developer >> Creating a Search Stored Procedure Thank you very much.




> >
> > I would like to create a GetPerson stored procedure that takes any
> combination of column values as parameters and searches based on them.
> If a particular column wasn't included then it's treated as "all" for
> that column. How would I build the query within the proc?
> >
>
>
> The general way of doing this (although doesn't make good use of
> indexes) is like this:
>
> Create Procedure GetPerson




> AS
>
> SELECT <your list>
> FROM Person
> WHERE




> GO
>
> Zach
>
 
 
ChrisMartin





PostPosted: Thu Jul 01 16:14:01 CDT 2004 Top

SQL Server Developer >> Creating a Search Stored Procedure Thank you very much



> Try something like
>
> SELECT <column_list>
> FROM Person


> ....
>


> > I have the following table structure
> >
> > Table: Person
> > Columns:
> > ID: int identity
> > FirstName: varchar(50)
> > LastName: varchar(50)
> > BirthDate: datetime
> >
> > I would like to create a GetPerson stored procedure that takes any
> combination of column values as parameters and searches based on them. If a
> particular column wasn't included then it's treated as "all" for that
> column. How would I build the query within the proc?
> >
> > Create Procedure GetPerson
> > (




> > )
> > AS
> > ????
> > GO
> >
> > If a caller called the procedure with no parameters then all person

> "Jones" then all person records with the last name "Jones" would be returned
> (Exact match, not considering LIKE at this time)
> >


> etc.
> >
> > I'm trying to keep from having a bunch of procs to maintain.
> >
> > Thanks in advance
> > Chris
>
>
>
 
 
ChrisMartin





PostPosted: Thu Jul 01 16:38:01 CDT 2004 Top

SQL Server Developer >> Creating a Search Stored Procedure how does it work if birthdate is a nullable column? I tried it and coalesce comes back with where birthdate = null which returns NO rows

Thanks again.




> >
> > I would like to create a GetPerson stored procedure that takes any
> combination of column values as parameters and searches based on them.
> If a particular column wasn't included then it's treated as "all" for
> that column. How would I build the query within the proc?
> >
>
>
> The general way of doing this (although doesn't make good use of
> indexes) is like this:
>
> Create Procedure GetPerson




> AS
>
> SELECT <your list>
> FROM Person
> WHERE




> GO
>
> Zach
>
 
 
ChrisMartin





PostPosted: Thu Jul 01 16:45:01 CDT 2004 Top

SQL Server Developer >> Creating a Search Stored Procedure Thanks. I actually like that option better than coalesce becuase if the column to be searched is a nullable column then any rows with a null isn't returned.

For instance
...


won't return rows if birthdate is nullable and null value exists.

Thanks
Chris




> Hi
>
> You could use the following type of construct
>
> Create Procedure GetPerson
> (




> )
> AS
> SELECT
> ID,
> FirstName,
> LastName,
> BirthDate
> FROM Person




>
>
> John
>


> > I have the following table structure
> >
> > Table: Person
> > Columns:
> > ID: int identity
> > FirstName: varchar(50)
> > LastName: varchar(50)
> > BirthDate: datetime
> >
> > I would like to create a GetPerson stored procedure that takes any
> combination of column values as parameters and searches based on them. If a
> particular column wasn't included then it's treated as "all" for that
> column. How would I build the query within the proc?
> >
> > Create Procedure GetPerson
> > (




> > )
> > AS
> > ????
> > GO
> >
> > If a caller called the procedure with no parameters then all person

> "Jones" then all person records with the last name "Jones" would be returned
> (Exact match, not considering LIKE at this time)
> >


> etc.
> >
> > I'm trying to keep from having a bunch of procs to maintain.
> >
> > Thanks in advance
> > Chris
>
>
>
 
 
Steve





PostPosted: Thu Jul 01 17:40:20 CDT 2004 Top

SQL Server Developer >> Creating a Search Stored Procedure You could use

WHERE COALESCE(Birthdate,'99991231') =

will be searching for those born on 9999-12-31...

Steve Kass
Drew University



>Thanks. I actually like that option better than coalesce becuase if the column to be searched is a nullable column then any rows with a null isn't returned.
>
>For instance
>...

>
>won't return rows if birthdate is nullable and null value exists.
>
>Thanks
>Chris
>
>

>
>
>
>>Hi
>>
>>You could use the following type of construct
>>
>>Create Procedure GetPerson
>> (




>> )
>>AS
>>SELECT
>> ID,
>> FirstName,
>> LastName,
>> BirthDate
>>FROM Person




>>
>>
>>John
>>


>>
>>
>>>I have the following table structure
>>>
>>>Table: Person
>>>Columns:
>>> ID: int identity
>>> FirstName: varchar(50)
>>> LastName: varchar(50)
>>> BirthDate: datetime
>>>
>>>I would like to create a GetPerson stored procedure that takes any
>>>
>>>
>>combination of column values as parameters and searches based on them. If a
>>particular column wasn't included then it's treated as "all" for that
>>column. How would I build the query within the proc?
>>
>>
>>>Create Procedure GetPerson
>>> (




>>> )
>>>AS
>>>????
>>>GO
>>>
>>>If a caller called the procedure with no parameters then all person
>>>
>>>

>>"Jones" then all person records with the last name "Jones" would be returned
>>(Exact match, not considering LIKE at this time)
>>
>>

>>>
>>>

>>etc.
>>
>>
>>>I'm trying to keep from having a bunch of procs to maintain.
>>>
>>>Thanks in advance
>>>Chris
>>>
>>>
>>
>>
>>

 
 
ChrisMartin





PostPosted: Thu Jul 01 17:48:02 CDT 2004 Top

SQL Server Developer >> Creating a Search Stored Procedure Thanks.



> You could use
>
> WHERE COALESCE(Birthdate,'99991231') =

> will be searching for those born on 9999-12-31...
>
> Steve Kass
> Drew University
>

>
> >Thanks. I actually like that option better than coalesce becuase if the column to be searched is a nullable column then any rows with a null isn't returned.
> >
> >For instance
> >...

> >
> >won't return rows if birthdate is nullable and null value exists.
> >
> >Thanks
> >Chris
> >
> >

> >
> >
> >
> >>Hi
> >>
> >>You could use the following type of construct
> >>
> >>Create Procedure GetPerson
> >> (




> >> )
> >>AS
> >>SELECT
> >> ID,
> >> FirstName,
> >> LastName,
> >> BirthDate
> >>FROM Person




> >>
> >>
> >>John
> >>


> >>
> >>
> >>>I have the following table structure
> >>>
> >>>Table: Person
> >>>Columns:
> >>> ID: int identity
> >>> FirstName: varchar(50)
> >>> LastName: varchar(50)
> >>> BirthDate: datetime
> >>>
> >>>I would like to create a GetPerson stored procedure that takes any
> >>>
> >>>
> >>combination of column values as parameters and searches based on them. If a
> >>particular column wasn't included then it's treated as "all" for that
> >>column. How would I build the query within the proc?
> >>
> >>
> >>>Create Procedure GetPerson
> >>> (




> >>> )
> >>>AS
> >>>????
> >>>GO
> >>>
> >>>If a caller called the procedure with no parameters then all person
> >>>
> >>>

> >>"Jones" then all person records with the last name "Jones" would be returned
> >>(Exact match, not considering LIKE at this time)
> >>
> >>

> >>>
> >>>

> >>etc.
> >>
> >>
> >>>I'm trying to keep from having a bunch of procs to maintain.
> >>>
> >>>Thanks in advance
> >>>Chris
> >>>
> >>>
> >>
> >>
> >>
>
>