Declared datetime Parameter not working correctly in sql 2014

Question

Declared datetime Parameter not working correctly in sql 2014

Lee Hopkins

SSC Eights!

Points: 960
More actions
June 20, 2019 at 1:16 pm

#3655762
We have a Data base we migrated to 2014 from 2008, everything is working fine except we have a stored proc that has the following
it just hangs, but if I replace the parameter in the select statement with getdate() it works. or if i change the compatibility mode to 2008.
when I am debug and hover over the @today I can see it is properly set to todays date. very very strange
```
Declare @today datetime
Set @today = getdate()

select * from table where insertdate < @today
```

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

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

DinoRS SSCrazy Points: 2683 More actions · Answer 1

This might have to do with what SQL Server would expect as a Default value, GetDate()'s Default Value is non existent so precompiling query plans (which is what sp's are supposed to do in order to work faster than plain, dynamic queries.

You could try some sort of approach like this instead:

ALTER PROC DisplayDate 
(
    @DateVar DATETIME = NULL
) AS 
BEGIN
    set @DateVar=ISNULL(@DateVar,GETDATE())

    --the SP stuff here
    SELECT @DateVar
END
GO

Phil Parkin SSC Guru Points: 246993 More actions · Answer 2

--Deleted

This reply was modified 5 years, 6 months ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Lee Hopkins SSC Eights! Points: 960 More actions · Answer 3

my question is why does it work with compatibility mode 2008 not with comparability mode 2014?

because I have 100's of procs that use the declare @today datetime

Phil Parkin SSC Guru Points: 246993 More actions · Answer 4

Lee Hopkins wrote:

my question is why does it work with compatibility mode 2008 not with comparability mode 2014? because I have 100's of procs that use the declare @today datetime

Can you provide a self-contained example which demonstrates this, so that we can verify it too?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kaj SSCommitted Points: 1608 More actions · Answer 5

I'm guessing this has to do with the new Cardinality Estimator that was introduced with SQL Server 2014.

We've seen some strange goings on with this feature if the statistics aren't quite up-to-date. So I'd try to first instruct SQL Server to use the old Cardinality Estimator by adding OPTION(QUERYTRACEON 9481) to the query.

If the query runs as expected with this trace flag set, you know it's the Cardinality Estimator that makes SQL Server choose a bad execution plan (like doing a table scan instead of doing an index seek).

You might also try to update the statistics on the table and see if that changes anything.

Lee Hopkins SSC Eights! Points: 960 More actions · Answer 6

Lee Hopkins

SSC Eights!

Points: 960

June 20, 2019 at 3:30 pm

#3655815

good suggestions i will take a look.

Lee Hopkins SSC Eights! Points: 960 More actions · Answer 7

Thanks for the help using OPTION(QUERYTRACEON 9481) , corrected the issue I also tried option (recompile) and it worked also. so the Cardinality Estimator is a new feature to 2014, to help pick a better excursion plan?