February 16, 2012 at 3:02 am
wat is the difference between the following code:-
USE EVENT
GO
DECLARE @CurrentDate datetime
SET @CurrentDate = getdate()
UPDATE dbo.EVENT_TASK
SET reminder_sent_flag = CASE WHEN (evt_task_reminder_date='1900-01-01 00:00:00.000' OR evt_task_reminder_date > @CurrentDate )
THEN 0
ELSE 1
END
GO
----------------- AND-----------------------
USE EVENT
GO
UPDATE dbo.EVENT_TASK
SET reminder_sent_flag = CASE WHEN (evt_task_reminder_date='1900-01-01 00:00:00.000' OR evt_task_reminder_date > getdate())
THEN 0
ELSE 1
END
GO
February 16, 2012 at 3:10 am
The top script is using variables to pass in the current date whereas the bottom script passes in the current date without using variables
Both should do the same thing
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
February 16, 2012 at 3:16 am
Why the use of variables explicitly?
whats the benifit of using variables?are the two different in performance?
February 16, 2012 at 3:25 am
For me it makes the query a little easier to read - this is such a small script it doesn't really make a difference either way and I would say is down to personal preference.
In terms of performance I would not have thought there would be any difference between them
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
February 16, 2012 at 6:07 am
One thing that might make a difference is if you use the variables multple times or call gETDATE() multiple times, the latter can result in different values. If that's what you want, great. If not, using variables can ensure that the values are always the same.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 16, 2012 at 6:31 am
It is there for reusability. Using the variable is to ensure that the value will not be changed during the execution of the batch (if it is the requirement of the application).
Since GETDATE() is evaluated at runtime it's value may change during the execution of the batch.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply