MS SQLCE - Help with a SQL query please

Author

Help with a SQL query please

Shadow Guild

2006-03-21, 1:30 pm

I realize for many this is not a difficult thing, however I am still learning
and would like some assistance. I am trying to write a query which will
search based on an unknown number of search fields. What I mean is that some
of the fields may be known, and some may not, and I want to display all
results which match the entered criteria. What I have so far is going to be
posted at the bottom.

I am using an ASP.NET website as the interface for the database, and can
control what is sent to the procedure.

CREATE PROCEDURE dbo.SearchDog
@tagnum int,
@ownername varchar(50),
@address varchar(50),
@city varchar(50),
@telephone varchar(50),
@doggender varchar(50),
@dogbreed varchar(50),
@dogage varchar(50),
@dogcolour varchar(50),
@dogname varchar(50),
@applicationdate varchar(50)
AS
IF @tagnum = NULL
SET @tagnum='%'
IF @ownername = NULL
SET @ownername='%'
IF @address = NULL
SET @address='%'
IF @city = NULL
SET @address='%'
IF @telephone = NULL
SET @telephone='%'
IF @dogbreed = NULL
SET @dogbreed='%'
IF @doggender = NULL
SET @doggender='%'
IF @dogage = NULL
SET @dogage='%'
IF @dogcolour = NULL
SET @dogcolour='%'
IF @dogname = NULL
SET @dogname='%'
IF @applicationdate = NULL
SET @applicationdate='%'

SELECT *
FROM DogRegistry
WHERE (
TagNum LIKE @tagnum AND
OwnerName LIKE @ownername AND
Address LIKE @address AND
City LIKE @city AND
Telephone LIKE @telephone AND
DogGender LIKE @doggender AND
DogBreed LIKE @dogbreed AND
DogAge LIKE @dogage AND
DogColour LIKE @dogcolour AND
DogName LIKE @dogname AND
ApplicationDate LIKE @applicationdate
)
GO

Brian Nantz

2006-03-21, 8:30 pm

This really is not the right newsgroup. What is the porblem with your
query??

Does it not work like you expect it too?

see http://msdn2.microsoft.com/en-US/library/ms179859.aspx
OR
use the books oline (BOL) for SQL server it is pretty nice.

Shadow Guild wrote:
> I realize for many this is not a difficult thing, however I am still learning
> and would like some assistance. I am trying to write a query which will
> search based on an unknown number of search fields. What I mean is that some
> of the fields may be known, and some may not, and I want to display all
> results which match the entered criteria. What I have so far is going to be
> posted at the bottom.
>
> I am using an ASP.NET website as the interface for the database, and can
> control what is sent to the procedure.
>
> CREATE PROCEDURE dbo.SearchDog
> @tagnum int,
> @ownername varchar(50),
> @address varchar(50),
> @city varchar(50),
> @telephone varchar(50),
> @doggender varchar(50),
> @dogbreed varchar(50),
> @dogage varchar(50),
> @dogcolour varchar(50),
> @dogname varchar(50),
> @applicationdate varchar(50)
> AS
> IF @tagnum = NULL
> SET @tagnum='%'
> IF @ownername = NULL
> SET @ownername='%'
> IF @address = NULL
> SET @address='%'
> IF @city = NULL
> SET @address='%'
> IF @telephone = NULL
> SET @telephone='%'
> IF @dogbreed = NULL
> SET @dogbreed='%'
> IF @doggender = NULL
> SET @doggender='%'
> IF @dogage = NULL
> SET @dogage='%'
> IF @dogcolour = NULL
> SET @dogcolour='%'
> IF @dogname = NULL
> SET @dogname='%'
> IF @applicationdate = NULL
> SET @applicationdate='%'

>
> SELECT *
> FROM DogRegistry
> WHERE (
> TagNum LIKE @tagnum AND
> OwnerName LIKE @ownername AND
> Address LIKE @address AND
> City LIKE @city AND
> Telephone LIKE @telephone AND
> DogGender LIKE @doggender AND
> DogBreed LIKE @dogbreed AND
> DogAge LIKE @dogage AND
> DogColour LIKE @dogcolour AND
> DogName LIKE @dogname AND
> ApplicationDate LIKE @applicationdate
> )
> GO
>

Sponsored Links