TSQL -> Simple Scalar UDF (User Defined Function) in TSQL

This UDF function returns delivery price depending on customer's location:
IF EXISTS (SELECT * FROM sys.objects
   WHERE object_id = OBJECT_ID(N '[dbo].[fn_getdeliveryprice]')
   AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_getdeliveryprice];
GO

CREATE FUNCTION dbo.fn_getdeliveryprice
   (@country Varchar(50), @city Varchar(50)) RETURNS Float
AS
BEGIN
DECLARE @topay Float;
/*
for not identified customer's location delivery price is 45 euro
*/
SET @topay = 45;

IF @country = 'iceland'
BEGIN
   SET @topay=8.15;
END
IF @country = 'poland'
BEGIN
  IF @city = 'warsaw'
   SET @topay=7.85;
  ELSE
   SET @topay=9.75;
END
/* ... here more price options can be placed ... */

RETURN @topay;
END

GRANT EXECUTE ON OBJECT::[dbo].[fn_getdeliveryprice] TO george;
GO


Then we can call this function using EXECUTE command:
DECLARE @myPrice FLOAT;

EXEC @myPrice = dbo.fn_getdeliveryprice 'poland', 'krakow';

PRINT @myPrice;
Reciving in the message window: 9.75

Or call function for every row in SELECT statement:
SELECT CustomerID, CustomerName, dbo.fn_getdeliveryprice(Country, City) AS DelivPay
FROM Customers
WHERE CustomerID < 3;

The results will came as following:
CustomerIDCustomerNameDelivPay
1Angelina Alba28.5
2Jessica Simpson8.15


sqlexamples.info