Help with a query

Question

Help with a query

SSC Enthusiast

Points: 184

April 13, 2021 at 6:09 pm

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!

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

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

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

Puiu SSC Enthusiast Points: 184 More actions · Answer 2

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

ScottPletcher SSC Guru Points: 100948 More actions · Answer 3

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) A socialist is someone who will give you the shirt off *someone else's* back.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

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

Puiu SSC Enthusiast Points: 184 More actions · Answer 5

Puiu

SSC Enthusiast

Points: 184

April 13, 2021 at 6:50 pm

#3870658

It works perfectly! Thank you!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

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