Feeding selections into a stored procedure

Hello All,

I'm not really sure where to post this as I'm not quite sure how to approach the problem; either whether it's an SQL problem or shoudl be addressed on the form.

Anyway, on an .aspx page (using VB.NET) I have a drop down box and two text boxes. The user first selects a centre from the drop down and then enters a start and end date in the two text boxes. The user then clicks on a button which lists the results of a SQL server stored procedure using the specified parameters in a datagrid. All this is fine and works.

Users however have requested an 'All centers' option in the drop down, which if selected, essentially means that instead of listing the results for a single centre, all centres are listed for the start and end dates specified.

This is where I'm having problems. How do I feed this into my stored procedure? Is it a change in the procedure or something I need to do on the form?

Any help appreciated.

Mo

P.S. stored procedure look like this:

------

@.centreid int,
@.startdate varchar(20),
@.enddate varchar(20)

AS

SELECT centreid, datecreated, centrename

FROM tblcentres

WHERE (DataLength(@.startdate) = 0 OR CentreID = @.CentreID)
AND (DataLength(@.startdate) = 0 OR datecreated >= @.startdate)
AND (DataLength(@.enddate) = 0 OR datecreated <= @.enddate)

I would pass a 0 or some other number like 9999 which is not in the list of centerId's and do a check in the stored proc. If its the 0 or 9999 you passed remove that from the where condition. Something like this:

IF @.centerId = 0SELECT centreid, datecreated, centrenameFROM tblcentresWHERE (DataLength(@.startdate) = 0OR datecreated >= @.startdate)AND (DataLength(@.enddate) = 0OR datecreated <= @.enddate)ELSE SELECT centreid, datecreated, centrenameFROM tblcentresWHERE (DataLength(@.startdate) = 0OR CentreID = @.CentreID)AND (DataLength(@.startdate) = 0OR datecreated >= @.startdate)AND (DataLength(@.enddate) = 0OR datecreated <= @.enddate)

|||

Is this part right:

WHERE (DataLength(@.startdate) = 0 OR CentreID = @.CentreID)?

Seems like you had the right idea for startdate/enddate, but you didn't implement it correctly for CentreID. You could change it to:

WHERE (@.CentreID=-1 OR CentreID = @.CentreID) then have the "All Centres" value -1. Or you can set the options value to a blank, have the sqldatasource convert blanks to nulls, and check for IS NULL. Any of those work.

I personally prefer to use the blank/null approach as most of the dropdown values I use are values from an identity column, and the field can not possibly be blank (It's marked not null). If it's not from an identity column, and is some kind of varchar field, then I'll simply use "All" as the value, and if someone put that into the database and they want to specifically search for it, they are sol.

|||Good catch Motley. I just cut n paste'ed the OP's query to give him/her an idea of how to approach the problem.|||Thanks to both of you for the advice and also for correcting the errors in my SQL.

Your suggestion(s) worked perfectly!

Mo