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