|
|
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:
| CustomerID | CustomerName | DelivPay |
| 1 | Angelina Alba | 28.5 |
| 2 | Jessica Simpson | 8.15 |
|