Stored procedure to select between dates?

sql tsql date stored-procedures

13,756

Solution 1

Instead of exec you should be using sp_executesql which allows for use of parameters, avoiding a risk of Sql injection and avoiding potential issues with dates passed as strings. First parameter is a query, second is a list of parameters and their types and the rest are parameter values.

alter PROCEDURE [dbo].[AutoCompleate] 
     @DateFrom datetime,
     @DateTo datetime,
     @SearchField varchar(50)
     AS

     -- V1.0 : ShaunM : 15 jun 2012
        --  AutoComplete textbox

    declare @sql nvarchar(max)
    set @sql = 'SELECT DISTINCT ' 
             + quotename(@SearchField)
             + ' FROM SchemaAudit'
             + ' WHERE [Date] between @from AND @to ORDER BY '
             + quotename(@SearchField)
             + ' ASC'

     exec sp_executesql @sql, 
                        N'@from datetime, @to datetime', 
                        @from = @DateFrom, @to = @DateTo

Now, about start and end dates, what exactly you want to do?

Solution 2

Use BETWEN

Where StartDate BETWEEN @DateFrom and @DateTo

EDIT: As Nalaka526 pointed out, I missed the EndDate, You can't use BETWEEN with two fields, You need to cast it to varchar and use >= and <= to compare between the range. Curt solution is doing that.

 WHERE StartDate >= ' + Convert(varchar(20),@DateFrom) + 'AND EndDate >= ' + Convert(varchar(20),@DateTo) + '

Solution 3

You would need to CAST the date parameters as varchar:

exec ('
     SELECT DISTINCT ' +
     @SearchField + ' FROM SchemaAudit WHERE StartDate >= "' + CAST(@DateFrom as varchar(20)) + '" AND EndDate >= "' + CAST(@DateTo as varchar(20)) + '"
                 ORDER BY ' + @SearchField +' ASC')

13,756

Author by

Pomster

Updated on June 04, 2022

Comments

Pomster about 2 years

I am writing a stored proc to Select information, i would like it to only select between dates?

This is what it looks like now:

ALTER PROCEDURE [dbo].[AutoCompleate] 
 @DateFrom datetime,
 @DateTo datetime,
 @SearchField varchar(50)
 AS

 -- V1.0 : ShaunM : 15 jun 2012
    --  AutoComplete textbox

 exec ('
 SELECT DISTINCT ' +
 @SearchField + ' FROM SchemaAudit 
             ORDER BY ' + @SearchField +' ASC')

I want the select to run for entry's into the database between @DateTo and DateFrom, Does any one know how to do this?

Curtis about 12 years

@HackedByChinese Cheers, there were some other syntax issues too which I've now corrected
Pomster about 12 years

Cast , Datefrom, DateTo and the numbers 20 are all underlined in red.
Pomster about 12 years

Could you put it in the same format as my exec( .... I can't get these commers right (')
Habib about 12 years

exec (' SELECT DISTINCT ' + @SearchField + ' FROM SchemaAudit WHERE StartDate >= ' +Convert(varchar(20), @DateFrom) + 'AND WHERE EndDate >= ' + Convert(varchar(20),@DateTo) + ' ORDER BY ' + @SearchField +' ASC')
Pomster about 12 years

Invalid column name startdate, Invalid Column name EndDate
Chris Gessler about 12 years

Wouldn't this still be subject to injection attack due to the @SearchField usage?
Nikola Markovinović about 12 years

@Chris- Yes, it should be quoted. Editing.
Pomster about 12 years

Convert, varchar and 20 are all underlined in red?
Nikola Markovinović about 12 years

@Pomster Column names are yours. Could you please post a working query (non-dynamic sql kind of query? Simple select?)
Pomster about 12 years

Start and end date i don't what they do, u guys keep putting them there? i just what the query to be between my two date parameters
Nikola Markovinović about 12 years

@Pomster I copied your original alter proc, section I would like it to do something like:
Pomster about 12 years

Yes i was trying to give the idea of what i wanted, sorry i see every one now has thought to use start and end date.
Pomster about 12 years

Thanks it says invalid column name date
Nikola Markovinović about 12 years

@Pomster Instead of [Date] put your date column name.