Passing parameters into a Table-Valued Function

Question

I have a TVF which needs 2 parameters a countrycode which is fixed within my general select statement

DECLARE @CountryCode as nvarchar(2)
SET @CountryCode = 'GB'

and a Date of which there will be many in my Select statement result

My TVF needs to pick up the Countrycode and date at runtime - if that's possible!

CREATE function [dbo].[V3_Pricelist_Country_lvl] (@Country as nvarchar(2), @Date as date)
returns table
as
return
SELECT @Country as Country, pl.SKU, pl.[RRP W/O TAX], pl.[BASE PRICE]
FROM V3_PriceList pl 
WHERE pl.Country = @Country and @Date between pl.Date and pl.DateEnd and [DISCONTINUED DATE] is null

I have tried to write my calling code as a left join like this

left join V3_PriceList_Country_lvl(@CountryCode, a.[Week_ending]) pl on pl.SKU = a.UID

but I get the following error message

The multi-part identifier "a.Week_ending" could not be bound.

If I could fix the date into a Declare variable it would work but the date will change on every row for every country and SKU. Apart from a regular Left join to the full V3_PriceList table how can make this work?

Abdul Rasheed · Accepted Answer · 2018-01-24 11:33:31Z

2

You need to use OUTER APPLY, no left join.

If you want to pass a value from outer table to your UDF as parameter, use CROSS APPLY/OUTER APPLY instead of INNER JOIN/OUTER JOIN.

Sample:-

SELECT ...
FROM [table_name] AS a
OUTER APPLY V3_PriceList_Country_lvl(@CountryCode, a.[Week_ending]) pl
WHERE ...

More info here...

edited Jan 24, 2018 at 11:33

answered Jan 24, 2018 at 11:21

Abdul Rasheed

6,7794 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MartinL Over a year ago

Thanks after a bit of faffing about I also found that it needs to go after all of the other left joins. Read up on the APPLY function here which explains it quite well mssqltips.com/sqlservertip/1958/…

Collectives™ on Stack Overflow

Passing parameters into a Table-Valued Function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related