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!
April 13, 2021 at 6:18 pm
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
April 13, 2021 at 6:20 pm
This is an exception that I address in the code. The parameters are either null or non-null simultaneously.
April 13, 2021 at 6:37 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 13, 2021 at 6:39 pm
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
April 13, 2021 at 6:50 pm
It works perfectly! Thank you!
April 14, 2021 at 3:34 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply