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 :)I'm a little confused. What is your question ?
did you mean except for incoporating NULLS ?
Cheers,
-Kilka|||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
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 :)|||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?|||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|||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.|||First, decide on precedence. What if the user passes all three parameters?
Assuming the precedence is LastName, FirstName, Date: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') )|||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.
