SQL Server Functions Examples

-------------------------------------------
-- Functions
-------------------------------------------

-- Create / Alter function
ALTER FUNCTION dbo.tax_fun
(@num_to_tax MONEY)
RETURNS MONEY
AS
	BEGIN
		RETURN @num_to_tax*1.155
	END

-- Calling it from SELECT statement
SELECT dbo.tax_fun(10) 

-- Calling it from DECLARE block 

DECLARE
	@v_catch_me MONEY
	@v_catch_me_again VARCHAR(25)
BEGIN
	SET @v_catch_me = dbo.tax_fun(10)
	SET @v_catch_me_again = UPPER('hello')
END 

-- Ex2 

CREATE FUNCTION dbo.two_letter_fun
(@word varchar(25))
RETURNS VARCHAR(2)
AS
BEGIN
	RETURN SUBSTRING(@word , 1 ,2)
END 

SELECT dbo.two_letter_fun('HELLO') 

SELECT dbo.two_letter_fun(lastName)
FROM employees 

DECLARE
	@v_catch_me varchar(25)
BEGIN
	SET @v_catch_me = dbo.two_letter_fun('HELLO')
	PRINT @v_catch_me
END 

-- P1 

ALTER FUNCTION dbo.email_fun
(@p_firstname varchar(25) ,
 @p_lastname varchar(25) )
 RETURNS varchar(25)
 AS
 BEGIN
	RETURN SUBSTRING( @p_firstname , 1 ,1) + substring(@p_lastName , 1 ,4) + '@someemail.com'
 END

 SELECT  dbo.email_fun ('moshe' , 'levi') 

 SELECT dbo.email_fun (lastName , firstName) AS 'MAIL'
 FROM employees 

 -- Ex3 

 CREATE FUNCTION dbo.prod_name_by_id_fun
 (@p_productid int)
 RETURNS VARCHAR(25)
 AS
 BEGIN
 DECLARE
	@v_productName varchar(25)
 SELECT @v_productName = productName FROM products WHERE productID = @p_productid
 RETURN @v_productName
 END 

 SELECT dbo.prod_name_by_id_fun(5) 

 SELECT dbo.prod_name_by_id_fun(productID)
 FROM products