Lowell (6/16/2015)

---

you don't put quotes around the parameter; it's already the right datatype.

your real function probably raises an error, sicne you have two variables:@tbl and @source_tbl, but only one...

Two things. One, a picture of the execution plan...

Maybe this:

ALTER FUNCTION [dbo].[history] (

@Since DATE,

@EarliestDated DATE = '6/1/2014',

@LatestDated DATE = NULL,

@Number FLOAT,

@Year INT,

@Substitution INT = 5000000

)

RETURNS TABLE

AS

RETURN (

WITH

N0(n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), --...

TomThomson (6/15/2015)

---

sgmunson (6/15/2015)

---

sqldummy79 (6/15/2015)

---

I have tried to find a blog or forum for the following issue, if someone could provide a link or the code, it would be greatly appreciated.

I...

Just an fyi since you figured out what was wrong, but it really helps if you format your code:

SELECT

preop.MPOG_Case_ID,

STUFF( (SELECT ',[' +...

Would help if you provided the actual execution plan for the fast and slow query.

No promises, but here is one possible rewrite:

CREATE FUNCTION [dbo].[SubQuery] (

@OnDate DATE,

@EarliestDated DATE,

@LatestDated DATE,

@Number FLOAT,

@Year INT,

@Substitution INT

)

RETURNS TABLE

AS

RETURN (

SELECT

...

phosplait (6/15/2015)

---

I thought maybe there was something I...

You are going to have do some work here as well:

FROM dbo.SubQuery(DATEADD(day, num - 1, @First), @DatedStart, @DatedEnd, @Number, @Year, @Sub)

phosplait (6/15/2015)

---

Alan.B (6/12/2015)

---

On a side note:

As a matter of practice, when using a cte tally table like you are, you may want to replace your WHERE clause with a...

phosplait (6/12/2015)

---

@jeff, there is no begin in the function. It is an inline function that does a cross apply with another inline function onto a range of dates

@Lynn, I would...

Please post further answers/questions here.

One...

Would help to see the DDL for the TVF, the table(s) involved, and the indexes defined.

On the first query where the data values are hardcoded you are getting key lookups...

Are the dates user selectable or are they determined by when the query is run?