Stored procedure, datetime

Question

I have a search form in a website where I have to do a search in all or one column to database. I have the following stored procedure and have problem with datetime. How will I do to allow it to be null?

I have problem with this both in stored procedure and i codebehind c#.

Hope you can help me!

ALTER PROCEDURE [dbo].[SearchPostit] ( 
@message varchar(1000)='', 
@writer varchar(50)='', 
@mailto varchar(100)='', 
@date Datetime ) AS 
SELECT  P.Message AS Postit, 
        P.Mailto AS 'Mailad till', 
        P.[Date] AS Datum , 
        U.UserName AS Användare
FROM PostIt P  LEFT OUTER JOIN 
     [User] U ON P.UserId=U.UserId

WHERE P.message LIKE '%'+@message+'%' AND P.Mailto LIKE '%'+@mailto+'%' 
AND P.Date LIKE '%'+@date+'%' AND U.UserName LIKE '%'+@writer+'%'
GROUP BY P.Message, P.Mailto, P.[Date], U.UserName

This is my codebehind when click on search button. But I got errormessage if no date is in textbox.... Postit p = new Postit(); DateTime dt=DateTime.Parse(TextBoxDate.Text); p.SearchPostit(TextBoxMessage.Text, TextBoxWriter.Text, TextBoxMailto.Text,dt);

Answer 1

You could change your where clause to something like

WHERE   P.message LIKE '%'+@message+'%' 
AND     P.Mailto LIKE '%'+@mailto+'%'  
AND     (
                @date IS NULL 
            OR  P.Date = @date
        )
AND     U.UserName LIKE '%'+@writer+'%' 

Answer 2

If I understand you correctly you want to have something like (P.Date LIKE.... OR @date IS NULL). You may also want to replace the LIKE part with a BETWEEN or >=/<= construct when working with DATETIMEs. However, looking at your statement the part AND U.UserName LIKE... turns the LEFT JOIN into an INNER JOIN and the whole WHERE clause with all the LIKEs is gonna kill your performance altogether.

Answer 3

I default the value to a null value and then handle the null case in the where clause which should then handle the times where you don't specify a date.

ALTER PROCEDURE [dbo].[SearchPostit] (
@message varchar(1000)='',
@writer varchar(50)='',
@mailto varchar(100)='',
@date Datetime = null ) AS
SELECT  P.Message AS Postit,
        P.Mailto AS 'Mailad till',
        P.[Date] AS Datum ,
        U.UserName AS Användare
FROM PostIt P  LEFT OUTER JOIN
     [User] U ON P.UserId=U.UserId

WHERE
      P.message LIKE '%'+@message+'%' AND
      P.Mailto LIKE '%'+@mailto+'%' AND
      ((@date is null) or (P.Date LIKE '%'+@date+'%')) AND
      U.UserName LIKE '%'+@writer+'%'
GROUP BY P.Message, P.Mailto, P.[Date], U.UserName

Answer 4

The DateTime.Parse() method requires a date string in a specific format. An empty TextBox string is just a "" which as you pointed out fails. An alternative would be to use the DateTime.TryParse() method which will not fail in the event the TextBox string is empty. The resultant DateTime time object would be set to the default values ie. 01-Jan-0001. Another aspect to consider is how often would you expect the Date Input to be blank/empty.

However in this case though, a value will always be passed to the sp.

DateTime dt=DateTime.Parse(TextBoxDate.Text) // fails if text is empty

However if you initialised the DateTime object first

DateTime dt = new DateTime();
dt = DateTime.Parse(TextBoxDate.Text)   // would fail if text is empty
DateTime.TryParse(TextBoxDate.Text, out dt) // would NOT fail if text is empty string

Use this code behind:

DateTime dt;
DateTime.TryParse(TextBoxDate.Text, out dt);

The sp may need to change if you use the above method to take into consideration the 01-JAN-0001 date if you need to.

Alternatively, you may want to pass the DateTime as a string to the sp. This would require that you accept a varchar instead of SQL DateTime and change the sp accordingly.

---

On a different note have a look at this slightly reworked SP

        ALTER PROCEDURE [dbo].[SearchPostit] ( 
@message varchar(1000), 
@writer varchar(50), 
@mailto varchar(100), 
@date Datetime ) AS

DECLARE @msg varchar(1002)
DECLARE @wrt varchar(52)
DECLARE @mlt varchar(102)
DECLARE @dte DateTime

IF (@message IS NULL)
BEGIN
    SET @msg = null
END
ELSE
    SET @msg = "%"+@message+"%"

    -- similar for mailto, writer values, there is probaby a cleaner way to do this though

IF (@date is not null)
BEGIN
    SET @ dte = CONVERT(Datetime, @date, 101) 
END 



SELECT  P.Message AS Postit, 
        P.Mailto AS "Mailad till", 
        P.[Date] AS Datum , 
        U.UserName AS Användare
FROM PostIt P  
LEFT OUTER JOIN 
     [User] U ON P.UserId=U.UserId
WHERE 
    P.message   LIKE COALESCE(@msg, P.Message)  AND 
    P.Mailto    LIKE COALESCE(@lt, P.Mailto)    AND 
    P.Date      LIKE @date                      AND
    U.UserName  LIKE COALESCE(@wrt, P.UserName) 
GROUP BY P.Message, P.Mailto, P.[Date], U.UserName

Answer 5

In C#, use a nullable DateTime. So something like:

DateTime parsedDateValue;
DateTime? dateValue = null;

If (DateTime.TryParse(DateTextBox.Text, out parsedDateValue)
   dateValue = parsedDateValue;

You need the non-nullable DateTime solely for use as an out parameter to the TryParse function. Using the DbCommand and DbParameters classes will convert a null DateTime? value to a database null for you.

With respect to the stored procedure, I'm not sure what you are trying to accomplish using: P.[Date] Like'%'+@date+'%'. If you use ( @Date Is Null Or P.[Date] = @Date ) and @Date is not null, then you will only return values that exactly equal P.[Date]. You have to be careful with equality comparisons on dates when the values are passed from .NET because of precision (.NET goes out to nanoseconds whereas standard SQL Server DateTime columns only go out to 300 ms). If you are attempting to find all values on the given day, you do something like:

( 
@Date Is Null Or 
    ( P.[Date] >= Cast(DateDiff(d, 0, @Date) As DateTime)
    And 
    P.[Date] < Cast(DateDiff(d, 0, @Date) + 1 As DateTime)
    )
)

The idea here is to strip the time element from passed @Date parameter. You can do that by finding the number of days from zero of the given date and then casting that number to a date. Similarly, you want the end point of your range to be midnight on the following day. This translates into finding all values where the date is greater than or equal to midnight on @Date and less the midnight on the following day.