ISNULL Date month

Question

Hi I have two dropdownlists, one with months and the other one with year. The user selects the submit month and year for the items they want to retrieve. In database the date is entered in full eg. 01/12/2009. There is an option for "All years" and "All months" in the dropdown lists but when users choose either they get null results. Many thanks. This is my query:

SELECT ItemID, YEAR(Submit) AS SubmitYear, MONTH(Submit) AS SubmitMonth 
FROM Items 
WHERE (YEAR(Submit) LIKE ISNULL(@YearPay, ''))
 AND (MONTH(Submit) LIKE ISNULL(@MonthPay, ''))

My parameter are:

<asp:ControlParameter ControlID="DropDownList1" DefaultValue="" Name="YearPay" PropertyName="SelectedValue" />

<asp:ControlParameter ControlID="DropDownList2" DefaultValue="" Name="MonthPay" PropertyName="SelectedValue" />

Answer 1

Here's one way to solve the problem:

 SELECT ItemID, YEAR(Submit) AS SubmitYear, MONTH(Submit) AS SubmitMonth 
 FROM Items 
 WHERE (YEAR(Submit) = @YearPay OR @YearPay IS NULL)
 AND (MONTH(Submit) = @MonthPay OR @MonthPay IS NULL)

That way, if you pass in NULL for either variable, that part of the WHERE clause will return true.

Answer 2

One fairly quick way is to make sure @YearPay and @MonthPay are INTEGER datatypes, then pass in (e.g.) -1 to indicate "ALL", so your query would become:

SELECT ItemID, YEAR(Submit) AS SubmitYear, MONTH(Submit) AS SubmitMonth 
FROM Items 
WHERE (YEAR(Submit) = @YearPay OR @YearPay = -1)
 AND (MONTH(Submit) = @MonthPay OR @MonthPay = -1)

However, for overall performance, I'd personally steer away from doing it in this way, using YEAR() and MONTH() as you won't get good index usage. Instead, I'd be tempted to change the query to just accept a date range, whereby the to and from dates are generated by your .NET code based on the selected dropdown items.

e.g.

SELECT ItemID, YEAR(Submit) AS SubmitYear, MONTH(Submit) AS SubmitMonth 
FROM Items 
WHERE (Submit >= @FromDate OR @FromDate IS NULL) 
    AND (Submit < @ToDate OR @ToDate IS NULL)

So, using the following examples for month/year selections:
Jan 2009 would result in @FromDate = '2009-01-01', @ToDate - '2009-02-01'
Any month, 2009 would result in @FromDate = '2009-01-01', @ToDate = '2010-01-01'
Any month, any year would result = @FromDate = NULL, @ToDate = NULL

Answer 3

Try this

select * from sys.tables where name like ''

and this

select * from sys.tables where name like '%%'

That being said, I totally agree with Nebakanezer's comment (and solution).