SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 issue when passing variable to query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jmhc101
Starting Member

3 Posts
Posted - 01/18/2013 :  08:34:01  Show Profile  Reply with Quote
hi,

this query works:

select ap.*
from date_lookup d
outer apply
dbo.udf_ActivePlacementNumbersByDay(d.date_full) ap
where d.date_full >= '20130101'
and d.date_full <= '20130103'

yet this one doesn't (it hangs):

declare @startDate date
declare @endDate date

set @startDate = '20130101'
set @endDate = '20130103'

select ap.*
from date_lookup d
outer apply
dbo.udf_ActivePlacementNumbersByDay(d.date_full) ap
where d.date_full >= @startDate
and d.date_full <= @endDate

I'm assuming this must be a data type issue for the date variables. Can someone assist me in how I can pass a variable into this query and what data types to use correctly.

If you need more context about the query - please let me know.

James K
Flowing Fount of Yak Knowledge

1733 Posts
Posted - 01/18/2013 :  13:20:11  Show Profile  Reply with Quote
It could be because of a bad execution plan, or it could be because of the data types. Whether it is due to data types is easy to check change your WHERE clause to:
where d.date_full >= CAST(@startDate as DATE)
and CAST(d.date_full <= @endDate as DATE)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3856 Posts
Posted - 01/18/2013 :  15:03:38  Show Profile  Reply with Quote
Is this used in a stored procedure? If so, it's possible you might be a victom of parameter sniffing.
http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000