Stored Function in SQL

Stored Function in SQL

• Function is mainly used in the case where it must return a value.
• Function can be called from SQL statements.
• You can have DML (insert,update, delete) statements in a function.
• Function returns 1 value only. 

Mysql:
Simple function to return cube

CREATE FUNCTION `calcube`(`PID` INT)
          RETURNS INT(11)
          RETURN PID * PID * PID

Function to return student division based on marks
DELIMITER $$

CREATE FUNCTION resultRemark(mark1 int,mark2 int,mark3 int,mark4 int,mark5 int)
RETURNS VARCHAR(50)
          DETERMINISTIC
BEGIN
         DECLARE lvl varchar(50);
DECLARE total int;
DECLARE percentage double;
SET total = mark1 + mark2 + mark3 + mark4 + mark5;
SET percentage = (total*100)/500;
         IF percentage >= 60 THEN
                SET lvl = '1st division';
         ELSEIF (percentage >= 50) THEN
                SET lvl = '2nd division';
         ELSEIF (percentage >= 40) THEN
               SET lvl = '3rd division';
ELSE
SET lvl = 'You are failed';
    END IF;

 RETURN (lvl);
END

MS SQL Server

Create function to change the date into different dateFormat equivalent to mysql date_format function

CREATE FUNCTION [dbo].[DATE_FORMAT](@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
    DECLARE @StringDate VARCHAR(32)
    SET @StringDate = @FormatMask
IF (CHARINDEX ('%Y' collate SQL_Latin1_General_CP1_CS_AS,@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, '%Y' collate SQL_Latin1_General_CP1_CS_AS,
                         DATENAME(YYYY, @Datetime))
IF (CHARINDEX ('%y' Collate SQL_Latin1_General_CP1_CS_AS,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, '%y' collate SQL_Latin1_General_CP1_CS_AS,
                         RIGHT(DATENAME(YYYY, @Datetime),2))
    IF (CHARINDEX ('%M' collate SQL_Latin1_General_CP1_CS_AS,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, '%M' collate SQL_Latin1_General_CP1_CS_AS,
                         DATENAME(MM, @Datetime))
    IF (CHARINDEX ('%b' collate SQL_Latin1_General_CP1_CS_AS,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, '%b' collate SQL_Latin1_General_CP1_CS_AS,
                                     LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX ('%m' collate SQL_Latin1_General_CP1_CS_AS,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, '%m' collate SQL_Latin1_General_CP1_CS_AS,
                  RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF (CHARINDEX ('%d' collate SQL_Latin1_General_CP1_CS_AS,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, '%d' collate SQL_Latin1_General_CP1_CS_AS,
                         RIGHT('0'+DATENAME(DD, @Datetime),2))
IF (CHARINDEX ('%h' collate SQL_Latin1_General_CP1_CS_AS,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, '%h' collate SQL_Latin1_General_CP1_CS_AS,
                         LEFT(RIGHT('0'+LTRIM(RIGHT(CONVERT(VARCHAR,                                    CONVERT(DATETIME,@Datetime), 100),7)),7),2))
    IF (CHARINDEX ('%H' collate SQL_Latin1_General_CP1_CS_AS,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, '%H' collate SQL_Latin1_General_CP1_CS_AS,
                         RIGHT('0'+DATENAME(HOUR, @Datetime),2))
IF (CHARINDEX ('%i' collate SQL_Latin1_General_CP1_CS_AS,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, '%i' collate SQL_Latin1_General_CP1_CS_AS,
                         RIGHT('0'+DATENAME(n, @Datetime),2))
IF (CHARINDEX ('%s' collate SQL_Latin1_General_CP1_CS_AS,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, '%s' collate SQL_Latin1_General_CP1_CS_AS,
                         RIGHT('0'+DATENAME(SECOND, @Datetime),2))
IF (CHARINDEX ('%S' collate SQL_Latin1_General_CP1_CS_AS,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, '%S' collate SQL_Latin1_General_CP1_CS_AS,
                         RIGHT('0'+DATENAME(SECOND, @Datetime),2))
   RETURN @StringDate
END

Use it using date_format(created,'%Y-%m-%d')