Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Posts
    15

    Unanswered: Need help writing stored procedure involving dates

    I am trying to write a stored procedure which would execute following logic:

    - The stored procedure takes 2 optional parameters @StartDate and @EndDate

    @StartDate Datetime = null
    @EndDate Datetime = null

    - Since the parameters are optional user can enter either one or can leave both blank.
    - If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
    - If user enters SD, query should use @StartDate as the SD and GetDate() as the ED
    - If user enters ED, query should use @EndDate as the ED and MIN() of the Date field as SD

    I was able to write query which did almost everything as is stated above expect for incorporating NULLs
    The query is as below

    CREATE PROCEDURE SearchDocumentTable

    @FName varchar(100) = null,
    @LName varchar(25) = null,
    @ID varchar(9) = null,
    @StartDate Datetime = null,
    @EndDate Datetime = null


    AS
    IF ( @StartDate IS NULL)
    Select @StartDate = MIN(DateInputted) from Document

    Select
    FName as 'First Name',
    LName as 'Last Name',
    ID as 'Student ID',
    Orphan as 'Orphan',
    DocumentType as 'Document Type',
    DocDesc as 'Description of the Document',
    DateInputted as 'Date Entered',
    InputtedBy as 'Entered by'

    From Document,DocumentTypeCodes
    Where FName LIKE ISNULL(@FName,'%')
    AND LName LIKE ISNULL(@LName,'%' + NULL)
    AND ID LIKE ISNULL(@ID,'%' + NULL)
    AND (DateInputted BETWEEN @StartDate AND ISNULL(@EndDate,GETDATE()) OR DateInputted IS NULL)
    AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode

    GO

    Any help would be appreciated
    Thanks in advance

  2. #2
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    I'm a little confused. What is your question ?

    did you mean except for incoporating NULLS ?

    Cheers,
    -Kilka

  3. #3
    Join Date
    Jul 2004
    Posts
    15
    I am sorry wasnt thinking right when I posted that to the forum in the morning. Let me try to explain my problem with an example.
    To make it simple lets say I have a simple table with 3 columns: FName, LName and Date

    HTML Code:
    FName      LName        Date
    Aab                          02/05/2005
    Abc          Bb             02/06/2005
    Aaaa        Bbb
                                   02/07/2005
    Baaaa       Bbb
    Baaca                       02/07/2005
    Caa          Bbbb          02/07/2005

    As can be seen that FName, LName and Date columns accept NULL values.
    Now I want to write a stored procedure which takes 4 parameters ( all of them are optional) @FName, @LName, @StartDate and @EndDate and do a search on this table.
    I am having trouble handling these NULL fields.

    When I execute the stored procedure, I should get results as follows:
    - If user enters @FName LIKE 'A%', sp should return only the rows where first name matches that format ( no null first name or null last names or null dates should be returned). Result should be first 3 rows with first names: Aab, Abc, Aaaa
    - Similarly when searching for start date and end date, procedure should return only the rows which match the date criteria ignoring null first name and last name fields

    I hope this example would prove helpful, since everytime I try to write a query I always end up getting rows with null fields.
    Thanks again for your time
    Last edited by itrap2003; 03-07-05 at 14:30.

  4. #4
    Join Date
    Feb 2005
    Posts
    78
    Do you really have
    '%' + NULL
    in the code? I would think you just want '%'.

    Are you getting any data back from this query when you have data that looks like your example?

  5. #5
    Join Date
    Jul 2004
    Posts
    15
    You guys are good sorry forgot to update the stored procedure.
    No, '%' + NULL doesnot return any records. So I changed the stored procedure and use only '%' for all null parameters and adds OR FIELDNAME IS NULL at the end:

    CREATE PROCEDURE SearchDocumentTable

    @FName varchar(100) = null,
    @LName varchar(25) = null,
    @ID varchar(9) = null,
    @StartDate Datetime = null,
    @EndDate Datetime = null

    AS

    IF ( @StartDate IS NULL)
    Select @StartDate = MIN(DateInputted) from Document

    Select
    FName as 'First Name',
    LName as 'Last Name',
    ID as 'Student ID',
    Orphan as 'Orphan',
    DocumentType as 'Document Type',
    DocDesc as 'Description of the Document',
    DateInputted as 'Date Entered',
    InputtedBy as 'Entered by'

    From Document,DocumentTypeCodes
    Where FName LIKE ISNULL(@FName,'%')
    AND (LName LIKE ISNULL(@LName,'%') OR LName IS NULL)
    AND (ID LIKE ISNULL(@ID,'%') OR ID IS NULL)
    AND (DateInputted BETWEEN @StartDate AND ISNULL(@EndDate,GETDATE()) OR DateInputted IS NULL)
    AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode

    GO

    This is the stored procedure and as can be seen it will return null fields when I pass last name, ID or date as the parameter.
    Any ideas how can I modify the stored procedure to avoid returning null fields.

    Thank you again guys, I really appreciate your help

  6. #6
    Join Date
    Feb 2005
    Posts
    78
    Do you get the correct results if you take out
    OR LName IS NULL
    and
    OR ID IS NULL
    and
    OR DateInputted IS NULL
    ?

    If this is not the case then please type out using the example data you used above the exact results you would like to see if all the input fields are NULL.

  7. #7
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    First, decide on precedence. What if the user passes all three parameters?

    Assuming the precedence is LastName, FirstName, Date:
    Code:
    Declare @t Table(Table_Pk int identity(1,1), FirstName varchar(3) Null, LastName varchar(3) Null, EndDate datetime Null)
    
    Insert @t (FirstName, LastName, EndDate)
    Select 'Kim', 'Cat', GetDate()
    Union
    Select 'Pat', 'Dog', Null
    Union
    Select 'Ted', Null, GetDate()
    Union
    Select 'Jim', Null, Null
    Union
    Select Null, 'Fox', GetDate()
    Union
    Select Null, 'Fox', Null
    Union
    Select Null, Null, GetDate()
    Union
    Select Null, Null, Null
    
    Select * From @t
    
    Declare @FirstName varchar(3)
    	, @LastName varchar(3)
    	, @EndDate datetime
    
    Select @LastName = 'F'
    
    Select *
    From @t
    Where (@LastName Is Not Null And LastName Like @LastName + '%')
    	Or 
    	(@LastName Is  Null And @FirstName Is Not Null And FirstName Like @FirstName + '%')
    	Or 
    	(@LastName Is  Null And @FirstName Is  Null And EndDate > = Coalesce(@EndDate, '01/01/1950') )
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    FYI, if you test for the IF statements and, inside the stored procedure, call a different function for each test to return the final result set, you will have no recompiles. This will make the overall execution of the stored proc much faster when you get larger recordsets. You also might want to consider having the developers use a checkbox for exact match. That sounds stupid, but the users will learn to love it if this table gets extremely large.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •