fbpx

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 

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…