First let us build the base table, Order_Master:
create table Order_Master
(Order_ID smallint not null
,Begin_Date datetime not null
,End_Date datetime not null
,Delivery_Location varchar(50) not null)
go
Now, let's put in some data into the table, Order_Master:
Insert into Order_Master (Order_ID, Begin_Date, End_Date, Delivery_Location)
select 1, getdate()-100, getdate()+100, 'New York'
union all
select 2, getdate()-50, getdate()+50, 'Washington D.C'
union all
select 3, getdate()-25, getdate()+25, 'Princeton'
union all
select 4, getdate()-12, getdate()+12, 'Dallas'
union all
select 5, getdate()-6, getdate()+6, 'Los Angeles'
go
Let's read from the table, Order_Master:
select * from Order_Master
go
Order_ID Begin_Date End_Date Delivery_Location
-------- ----------------------- ----------------------- --------------------
1 2007-12-01 14:06:04.087 2008-06-18 14:06:04.087 New York
2 2008-01-20 14:06:04.087 2008-04-29 14:06:04.087 Washington D.C
3 2008-02-14 14:06:04.087 2008-04-04 14:06:04.087 Princeton
4 2008-02-27 14:06:04.087 2008-03-22 14:06:04.087 Dallas
5 2008-03-04 14:06:04.087 2008-03-16 14:06:04.087 Los Angeles
Now let's come to the scenario.
1. For a given Order_ID and for a given input date, we need to find out what the begin date and end date for that Order_ID are.
2. We also need to find out whether the given input date falls within the begin date and end date for that order ID.
3. The output needs to be the Begin Date, the end date and a return value which would be 1 if the given input date falls within the begin date and end date for that order ID or 0 if it doesn't.
Given these requirements, a normal Stored Procedure would be like the following:
if exists (select 1 from sys.procedures
where type = 'P'
and user_name(schema_id) = 'dbo'
and [NAME] = 'DBP_ValidateOrderDates')
drop procedure [dbo].[DBP_ValidateOrderDates]
GO
CREATE PROCEDURE [dbo].[DBP_ValidateOrderDates]
@i_OrderNo varchar(6),
@i_ValidationDate datetime,
@o_Return bit = 0 output,
@o_BeginDate datetime = Null output,
@o_EndDate datetime = Null output
AS
BEGIN
SET NOCOUNT ON;
Select @o_BeginDate = Begin_Date
,@o_EndDate = End_Date
from dbo.Order_Master
where Order_ID = @i_OrderNo
IF @@rowcount = 0
Begin
Set @o_Return = 0
End
Else
If (@i_ValidationDate <= @o_EndDate and @i_ValidationDate >= @o_BeginDate)
Begin
set @o_Return = 1
End
Else
Begin
set @o_Return = 0
End
Set nocount off
END
Go
Now let's compile and then execute this SP for Order_ID = 1 and input date as today's date:
--Declaring the variables:
declare @i_OrderNo varchar(6),
@i_ValidationDate datetime,
@o_Return bit,
@o_BeginDate datetime,
@o_EndDate datetime
--Assigning values to input variables/parameters:
select @i_OrderNo = 1
,@i_ValidationDate = getdate()
,@o_Return = null
--Executing the SP with the given input parameters:
execute [dbo].[DBP_ValidateOrderDates]
@i_OrderNo
,@i_ValidationDate
,@o_Return output
,@o_BeginDate output
,@o_EndDate output
--Capturing the output:
select
@o_Return 'Return Value', @o_BeginDate 'Begin Date', @o_EndDate 'End Date'
Here is the result set as expected. We got the begin and end dates for the given Order_ID (1) and the return value is 1 because the input date falls between the begin and end dates for that Order_ID:
Return Value Begin Date End Date
------------ ----------------------- -----------------------
1 2007-12-01 14:06:04.087 2008-06-18 14:06:04.087
Now let's execute this SP for the same Order_ID (1) but with the input date not falling within the begin and end dates for that Order_ID.
--Declaring the variables:
declare @i_OrderNo varchar(6),
@i_ValidationDate datetime,
@o_Return bit,
@o_BeginDate datetime,
@o_EndDate datetime
--Assigning values to input variables/parameters:
select @i_OrderNo = 1
,@i_ValidationDate = getdate()-200
,@o_Return = null
--Executing the SP with the given input parameters:
execute [dbo].[DBP_ValidateOrderDates]
@i_OrderNo
,@i_ValidationDate
,@o_Return output
,@o_BeginDate output
,@o_EndDate output
--Capturing the output:
select
@o_Return 'Return Value', @o_BeginDate 'Begin Date', @o_EndDate 'End Date'
Here is the result set as expected. We got the begin and end dates for the given Order_ID (1) and the return value is 0 because the input date does not fall between the begin and end dates for that Order_ID:
Return Value Begin Date End Date
------------ ----------------------- -----------------------
0 2007-12-01 14:06:04.087 2008-06-18 14:06:04.087
Now let's take another possible situation. If we were to input an Order_ID (101) that doesn't exist in the Order_Master table, we should be getting nulls for the begin and end dates but the return value should be 0. Let's execute this scenario.
--Declaring the variables:
declare @i_OrderNo varchar(6),
@i_ValidationDate datetime,
@o_Return bit,
@o_BeginDate datetime,
@o_EndDate datetime
--Assigning values to input variables/parameters:
select @i_OrderNo = 101
,@i_ValidationDate = getdate()
,@o_Return = null
--Executing the SP with the given input parameters:
execute [dbo].[DBP_ValidateOrderDates]
@i_OrderNo
,@i_ValidationDate
,@o_Return output
,@o_BeginDate output
,@o_EndDate output
--Capturing the output:
select
@o_Return 'Return Value', @o_BeginDate 'Begin Date', @o_EndDate 'End Date'
The result set matches with the expected result:
Return Value Begin Date End Date
------------ ----------------------- -----------------------
0 NULL NULL
Since the input Order_ID, 101 does not exist in the table, the Begin and End dates are null. Since they are null, the input date can not fall between null begin and end dates and hence is 0.
Issue:
You may observe that there are so many constructs in this SP:
One SELECT statement
Three set variable statements
One usage of @@rowcount to check existence of the record
Three Begin-End blocks
Two IF and one ELSE construct
The SP seems perfectly logical. It has a very logical flow to satisfy all our requirements. But
the question is "Do we really need so many constructs to staify our requirements?". The answer is "May be Not". Remember, all we have to do is assign proper value to the output variables/parameters based on the input variables and the data in the table, Order_Master.
Let's try to simplify the same SP. Below, I have replace the IF BEGIN-END, IF-ELSE and @@rowcount with one UPDATE CASE statement. Let's see how it looks now:
if exists (select 1 from sys.procedures
where type = 'P'
and user_name(schema_id) = 'dbo'
and [NAME] = 'DBP_ValidateOrderDates')
drop procedure [dbo].[DBP_ValidateOrderDates]
GO
CREATE PROCEDURE [dbo].[DBP_ValidateOrderDates]
@i_OrderNo varchar(6),
@i_ValidationDate datetime,
@o_Return bit = 0 output,
@o_BeginDate datetime = Null output,
@o_EndDate datetime = Null output
AS
BEGIN
SET NOCOUNT ON;
update a
set @o_Return = case
when @i_ValidationDate between a.Begin_Date and a.End_Date then 1
else 0
end
,@o_BeginDate = a.Begin_Date
,@o_EndDate = a.End_Date
from (Select Begin_Date, End_Date from dbo.Order_Master with (nolock)
where Order_ID = @i_OrderNo) a
Set nocount off
END
Go
Doesn't the SP look short now? One UPDATE statement takes care of all possibilities and reduces code even though in the original SP, we are not updating any table for that matter. Neither are we updating the table Order_Master in this refined version of the original SP. All we are doing is updating the output variables / parameters based on the input parameters and the corresponding data in the table Order_Master. And that's all that is expected from this SP. So why take the longer route as in the original version? Not necessary.
Now let's execute the refined SP:
Now let's compile and then execute this SP for Order_ID = 1 and input date as today's date. This is the first case above:
--Declaring the variables:
declare @i_OrderNo varchar(6)
,@i_ValidationDate datetime
,@o_Return bit
,@o_BeginDate datetime
,@o_EndDate datetime
--Assigning values to input variables/parameters:
select @i_OrderNo = 1
,@i_ValidationDate = getdate()
,@o_Return = null
--Executing the SP with the given input parameters:
execute [dbo].[DBP_ValidateOrderDates]
@i_OrderNo
,@i_ValidationDate
,@o_Return output
,@o_BeginDate output
,@o_EndDate output
--Capturing the output:
select
@o_Return 'Return Value', @o_BeginDate 'Begin Date', @o_EndDate 'End Date'
Here is the result set as expected. We got the begin and end dates for the given Order_ID (1) and the return value is 1 because the input date falls between the begin and end dates for that Order_ID:
Return Value Begin Date End Date
------------ ----------------------- -----------------------
1 2007-12-01 14:06:04.087 2008-06-18 14:06:04.087
Now let's execute this SP for the same Order_ID (1) but with the input date not falling within the begin and end dates for that Order_ID. This is the second case above.
--Declaring the variables:
declare @i_OrderNo varchar(6)
,@i_ValidationDate datetime
,@o_Return bit
,@o_BeginDate datetime
,@o_EndDate datetime
--Assigning values to input variables/parameters:
select @i_OrderNo = 1
,@i_ValidationDate = getdate()-200
,@o_Return = null
--Executing the SP with the given input parameters:
execute [dbo].[DBP_ValidateOrderDates]
@i_OrderNo
,@i_ValidationDate
,@o_Return output
,@o_BeginDate output
,@o_EndDate output
--Capturing the output:
select
@o_Return 'Return Value', @o_BeginDate 'Begin Date', @o_EndDate 'End Date'
Here is the result set as expected. We got the begin and end dates for the given Order_ID (1) and the return value is 0 because the input date does not fall between the begin and end dates for that Order_ID(1):
Return Value Begin Date End Date
------------ ----------------------- -----------------------
0 2007-12-01 14:06:04.087 2008-06-18 14:06:04.087
Now let's take another possible situation, i.e. the third situation above. If we were to input an Order_ID (101) that doesn't exist in the Order_Master table, we should be getting nulls for the begin and end dates but the return value should be
0. Let's execute this scenario.
--Declaring the variables:
declare @i_OrderNo varchar(6)
,@i_ValidationDate datetime
,@o_Return bit
,@o_BeginDate datetime
,@o_EndDate datetime
--Assigning values to input variables/parameters:
select @i_OrderNo = 101
,@i_ValidationDate = getdate()
,@o_Return = null
--Executing the SP with the given input parameters:
execute [dbo].[DBP_ValidateOrderDates]
@i_OrderNo
,@i_ValidationDate
,@o_Return output
,@o_BeginDate output
,@o_EndDate output
--Capturing the output:
select
@o_Return 'Return Value', @o_BeginDate 'Begin Date', @o_EndDate 'End Date'
The result set matches with the expected result:
Return Value Begin Date End Date
------------ ----------------------- -----------------------
0 NULL NULL
Since the input Order_ID, 101 does not exist in the table, the Begin and End dates are null. Since they are null, the input date can not fall between null begin and end dates and hence is 0.
We get the same results with the refined version of the SP but in one statement that in multiple If's and ELSE's and @@rowcount.
The UPDATE statement in this case doesn't perform any updates to the table Order_Master itself. It just updates the output parameters to the correct values.
This is a very simple example of using unrelated operations (UPDATE) where SELECT seems the only solution and thus reducing the amount of code needed to be written and executed. This article just demonstrates that coding flexibility in Microsoft SQL Server's T-SQL is profound. Based on individual situation, there are numerous possibilities in T-SQL that can reduce code and execution time as well. We will follow some more of such techniques in the forthcoming articles.
No comments:
Post a Comment