1

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
  • 6,486
  • 4
  • 32
  • 48
MartinL
  • 45
  • 1
  • 1
  • 7

1 Answers1

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...

Abdul Rasheed
  • 6,486
  • 4
  • 32
  • 48
  • 1
    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 https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply – MartinL Jan 24 '18 at 11:48