Help with a query

  • Hi all! I want to create a stored procedure that returns a result based on the value of some DateTime parameters. Table format is:

    CREATE TABLE [dbo].[Contracte](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [NrContract] [int] NOT NULL,
    [DataContract] [datetime] NOT NULL,
    [idOrganizator] [int] NOT NULL,
    [idExcursie] [int] NOT NULL,
    [idUser] [int] NOT NULL,
    CONSTRAINT [PK_Contracte] PRIMARY KEY CLUSTERED
    (
    [id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]


    CREATE PROCEDURE GetDocuments
    @Date1 datetime = null
    ,@Date2 datetime = null
    AS
    BEGIN
    select * from [Contracte] where ?????
    END

    I need a query to show me the records whose DataContract are in the range @Date1 and @Date2, only if @Date1 and @Date2 are not null. If @Data1 and @Data2 are null, then you must return all records regardless of the date.

    Thank you all!

  • What if one of the date parameters is populated but the other isn't?

     

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • This is an exception that I address in the code. The parameters are either null or non-null simultaneously.

  • First let's correct the PK.  Then the code for the proc.

    ALTER TABLE [dbo].[Contracte] DROP CONSTRAINT [PK_Contracte];
    ALTER TABLE [dbo].[Contracte] ADD CONSTRAINT [PK_Contracte]
    PRIMARY KEY CLUSTERED ( DataContract, id ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
    ON [PRIMARY];
    GO
    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO
    CREATE PROCEDURE GetDocuments
    @Date1 datetime = null
    ,@Date2 datetime = null
    AS
    BEGIN
    SELECT *
    FROM dbo.[Contracte]
    WHERE ((@Date1 IS NULL OR @Date2 IS NULL) OR
    (DataContract >= @Date1 AND DataContract <= @Date2))
    END

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • I would do this with dynamic SQL.   Build the basic query string and then add the WHERE clause only when both date parameters are null.    Using sp_ExecuteSQL allows you to pass the values of @Date1 and @Date2 to the dynamic SQL at execution time.

    An example follows:

    CREATE PROCEDURE dbo.GetDocuments
    @Date1 datetime = null
    ,@Date2 datetime = null
    AS
    BEGIN
    declare @SQL nvarchar(4000) = 'select id, NrContract, DataContract, idOrganizator, idExcursie, idUser from dbo.Contracte'

    if @date1 is not null and @date2 is not null
    begin
    set @SQL = @sql +CHAR(13)+' where DataContract >= @Date1 and DataContract < @Date2'
    end


    exec sp_executeSQL @SQL, N'@Date1 datetime, @Date2 datetime', @Date1 = @Date1, @Date2 = @Date2

    END
    GO

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • It works perfectly! Thank you!

  • I would not get into the habit of using OR to test variable values when trying to add flexibility to queries like this.   For one thing, you get a single query plan.    For another, it sometimes forces table scans.

    With dynamic SQL you get one plan for when you want to read the whole table, another for when you filter by date.    When filtering by date, the query could take advantage of a nonclustered index to do an index seek.   When asking for all rows, it can either scan the clustered index to avoid key lookups, or scan any nonclustered "covering" index.

    ("Covering" means the nonclustered index contains all columns, that the query requires from the table.)

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply