Searching SQL using asp and dates

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)

Searching SQL using asp and dates

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-22 : 09:13:35

Tracey writes "I have a search form (ASP) that searches a database table.

The table is as follows:

ID PK
ProjName Varchar
StartDate Date\time
EndDate Date\Time
Value money

I have created a view that converts the date fields to varchar(20) date code 103. It also uses a case statement that returns a % if the value is null.

The form contains 2 Date fields, i want to write a SQL query that will allow the user to either enter a % to return all dates or enter 2 date values and use them as between dates.

Can this be done using a stored procedure?

I have created a view that converts the date fields to varchar(20) date code 103. It also uses a case statement that returns a % if the value is null.

The stored procedure i have tried is:

Create Procedure Test

@ProjName varchar(50) = '%',
@StartDate varchar(20) = '%',
@StartDate2 Varchar(20) = '%',
@Enddate Varchar (20) = '%',
@value int = 0,
@value2 int = 0

As
Select * from testview where Projname like @projname and
@StartDate between @StartDate1 and @StartDate2
and value between @value and @Value1

***********
I want to be able to have the choice of either entering % or to enter a value for it to search on.

Would this be done on the ASP Page or within the Procedure.

I appreciate any help you can give me.

Thanks"

Jay99

468 Posts

Posted - 2002-03-22 : 10:11:14

Pass in NULL instead of '%' and use the actual dates rather than the varchars.


...
where
    StartDate between isnull(@date1,StartDate-1) and isnull(@date1,StartDate+1) and
...

You really can use the isnull(@variable,fieldvalue) for all the search criteria instead of the '%'. That is to say if you have ProjName as a filed, but this particular search wants all projects, instead of...


set @projName = '%'
...
where
    ProjName like @ProjName

...you can...


where
    ProjName like isnull(@ProjName,ProjName)

Jay
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources