TSQL -> Using UDF function inside JOIN

Suppose that we have to update delivery price in Invoices table. We'll use Update JOIN together with scalar UDF function fn_getdeliveryprice(@country, @city).
Pay attention that alternative way to do the same update is to use CURSOR as described in Using CURSOR in stored procedure (1) example.

USE [colombo]
GO

ALTER TABLE dbo.Invoice ADD DlvPrice FLOAT;
GO

-- this select retrives delivery price for every invoice
SELECT t1.DocNo,
dbo.fn_getdeliveryprice (t1.Country, t1.City) AS DlvPrice
FROM
  (SELECT a.DocNo, b.Country, b.City
   FROM dbo.Invoice AS a INNER JOIN dbo.Customers AS b
   ON a.CustomerID = b.CustomerID
   GROUP BY a.DocNo, b.Country, b.City) AS t1;
GO


Results will look like this:
DocNoDlvPrice
128.5
26
345
48.15
....

USE [colombo]
GO

/* update column DlvPrice in Invoice table with the calculated price using join */
UPDATE t1
SET t1.DlvPrice = dbo.fn_getdeliveryprice(t2.Country, t2.City)
FROM Invoice AS t1 INNER JOIN
  (SELECT a.DocNo, b.Country, b.City
   FROM dbo.Invoice AS a INNER JOIN dbo.Customers AS b
   ON a.CustomerID = b.CustomerID
   GROUP BY a.DocNo, b.Country, b.City) AS t2;
GO

-- see results:
SELECT DocNo, DlvPrice FROM dbo.Invoice;

Results will look like this:
DocNoDlvPrice
128.5
26
345
48.15
....

-- remove added column:
ALTER TABLE dbo.Invoice DROP COLUMN DlvPrice;
GO



sqlexamples.info