Wednesday, 6 January 2016

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

1 comment:

  1. SQL is a standard language for accessing databases. Its very helpful for the developer for finding data from many tables. And also perform insert, delete, update, etc operation on the tables.

    Thanks for sharing this query.......

    ReplyDelete