Need help build a dynamic query with Between dates in sql server 2008

Question

I have UI where a user can search on optional parameters.I am writing a SP and as a testing database I am using AdventureWorks.

For some reasons I dont seem to find a way how to include

Where ModifiedDate between ''' + @Fromdate + ''' And '''+ @ToDate + '''')

in my existing SP.Can you help?

My sp looks like this

 ALTER PROCEDURE SearchPerson_Dynamic
(
@FirstName NVARCHAR(50) = NULL, 
@LastName NVARCHAR(50) = NULL, 
@EmailAddress NVARCHAR(50) = NULL, 
@Phone NVARCHAR(25) = NULL,
@FromDate DATETIME =NULL,
@ToDate DATETIME =NULL

AS
DECLARE @sSQL NVARCHAR(MAX), @Where NVARCHAR(1000) = ''
SET @sSQL = 'SELECT * FROM [Person].[Contact]'

IF @FirstName is not null
SET @Where = @Where + 'AND FirstName = @_FirstName '
IF @LastName is not null
SET @Where = @Where + 'AND LastName = @_LastName '
IF @EmailAddress IS NOT NULL
SET @Where = @Where + 'AND EmailAddress = @_EmailAddress '
IF @Phone IS NOT NULL
SET @Where = @Where + 'AND Phone = @_Phone '

-- NEED TO INTEGRATE A SEARCH BETWEEN @FromDate and @ToDate 

IF LEN(@Where) > 0
SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3)

 EXEC sp_executesql @sSQL,
    N'@_FirstName VARCHAR(50), 
            @_LastName VARCHAR(50), 
            @_EmailAddress VARCHAR(50), 
            @_Phone NVARCHAR(25)',  
    @_FirstName = @FirstName, 
            @_LastName = @LastName, 
            @_EmailAddress = EmailAddress,
            @_Phone = @Phone
            MISSING @FROMDate and @ToDate  --HOW DO i DO IT HERE

One thing I have not done is that some fields EG Surname the user can select "ALL" in the UI from the drop down. How would I implement a search where I dont have an exact parameter?

Thanks again for your help

Always worth reading Erland Sommarskog's articles on dynamic search, if you haven't before: sommarskog.se/dyn-search.html. Also, what problem are you encountering with adding the @FromDate and @ToDate to the search? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 10, 2010 at 7:51
Thanks for your reply. Is it Correct to do If @FromDate is not null and @ToDate is not null Set where =@where + 'ModifiedDate' between ''' + @Fromdate + ''' And '''+ @ToDate + '''') Also in the executeSql do I simply add @_FromDate=@FromDate,@_ToDate =@ToDate — user9969
– user9969, Commented Nov 10, 2010 at 8:49

Jason Jong · Accepted Answer · 2010-11-11 02:37:17Z

3

If I was to rewrite your stored proc, I would do the following

ALTER PROCEDURE SearchPerson_Dynamic
(
@FirstName NVARCHAR(50) = NULL, 
@LastName NVARCHAR(50) = NULL, 
@EmailAddress NVARCHAR(50) = NULL, 
@Phone NVARCHAR(25) = NULL,
@FromDate DATETIME =NULL,
@ToDate DATETIME =NULL
)
as
begin

    SELECT * FROM [Person].[Contact]
    where
        FirstName = coalesce(@FirstName, FirstName) and
        LastName = coalesce(@LastName, LastName) and
        EmailAddress = coalesce(@EmailAddress, EmailAddress) and
        Phone = coalesce(@Phone, Phone) and
        ModifiedDate >= coalesce(@FromDate, FromDate) and
        ModifiedDate <= coalesce(@ToDate, ToDate)
end

This is better as its not executing text for the stored procedure and could be optimized.

If you want to use the dynamic sql, you could cast the date to a normal string, ie

IF @FromDate IS NOT NULL AND @ToDate IS NOT NULL
    SET @Where = @Where + 'AND ModifiedDate between ''' + cast( @FromDate as varchar(50) ) +  ''' and  ''' +  cast( @ToDate as varchar(50) ) +  ''' '

edited Nov 11, 2010 at 2:37

answered Nov 10, 2010 at 7:34

Jason Jong

4,3302 gold badges28 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pavel Urbančík Over a year ago

Such query will most likely do table scan.

Jason Jong Over a year ago

Quite true, unless you put in OPTION (RECOMPILE). FYI, there a real good article on this at sommarskog.se/dyn-search.html

user9969 Over a year ago

are you saying if you put Option(recompile) it will not do a table scan?

Jason Jong Over a year ago

@user231465 - when specifying the option (recompile), the compiler will see

Jason Jong Over a year ago

didnt finish my comment : @user231465 - when specifying the option (recompile), the compiler will see the fields your using cause it gets compiled each time its run and then use the proper indexes.

Collectives™ on Stack Overflow

Need help build a dynamic query with Between dates in sql server 2008

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related