addinga Func to an SP?

Question

addinga Func to an SP?

jbalbo

SSCertifiable

Points: 7583
More actions
March 29, 2013 at 7:33 am

#291605

Hi
First time doing this and not sure how(Even if its the proper way)
I created the function Getforperiod
ALTER FUNCTION [dbo].[GetForPeriod]
( @StartDate datetime, @EndDate datetime)
RETURNS TABLE
RETURN
SELECT Date
FROM Auxiliary.Calendar
WHERE date BETWEEN @StartDate AND @EndDate
which returns the dates for the period
I want to add this function to an SP that looks at appointment dates , the reason is, I want to setup a matrix where if an appointment does not fall on a date
I still want to display the date so its a clean.... say for January, 1 thru 31
I want to use @startdate and @Enddate as parameters....
Make sense?
Thanks
Joe

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

jbalbo (3/29/2013)
Hi
First time doing this and not sure how(Even if its the proper way)
I created the function Getforperiod
ALTER FUNCTION [dbo].[GetForPeriod]
( @StartDate datetime, @EndDate datetime)
RETURNS TABLE
RETURN
SELECT Date
FROM Auxiliary.Calendar
WHERE date BETWEEN @StartDate AND @EndDate
which returns the dates for the period
I want to add this function to an SP that looks at appointment dates , the reason is, I want to setup a matrix where if an appointment does not fall on a date
I still want to display the date so its a clean.... say for January, 1 thru 31
I want to use @startdate and @Enddate as parameters....
Make sense?
Thanks
Joe

Without seeing where you are using this and what the expected output is, what do you want from us, shots in the dark?

Since your function is an inline table valued function, you'd use it in your FROM clause in the query, probably joining the other data to it using a left outer join.

About the best I can give you without seeing anything.

Lowell SSC Guru Points: 323518 More actions · Answer 2

Joe you'll most likely use a procedure like this, this is just a model, but you can see how the proc might use CROSS APPLY (or a left join? it's your process!) and the parameters to go against some other data in some other table. the procedure can accept more parameters than just the ones the function is going to use also.

CREATE PROCEDURE [dbo].[DoStuff]

( @CustomerID int, @StartDate datetime, @EndDate datetime)

AS

BEGIN

SELECT

SOMETABLE.*,

myfn.*

FROM SOMETABLE

CROSS APPLY [dbo].[GetForPeriod](@StartDate, @EndDate) myfn

WHERE SOMETABLE.CustomerID = @CustomerID

END --PROC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

I would ask why do you need a function for this at all? All you need to do is directly join to your calendar table. I don't see that this function actually provides much benefit. Join directly to your calendar table with the date criteria.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lowell (3/29/2013)
Joe you'll most likely use a procedure like this, this is just a model, but you can see how the proc might use CROSS APPLY (or a left join? it's your process!) and the parameters to go against some other data in some other table. the procedure can accept more parameters than just the ones the function is going to use also.
CREATE PROCEDURE [dbo].[DoStuff]
( @CustomerID int, @StartDate datetime, @EndDate datetime)
AS
BEGIN
SELECT
SOMETABLE.*,
myfn.*
FROM SOMETABLE
CROSS APPLY [dbo].[GetForPeriod](@StartDate, @EndDate) myfn
WHERE SOMETABLE.CustomerID = @CustomerID
END --PROC

CROSS APPLY works like an INNER JOIN, it will lose rows where there is no match. I see the itvf working as the top level table to ensure that all values from the table are present and using a left outer join to get the rest of the data from the other tables. Of course, without seeing the code in the SP this is all guess work.