Skip to main content

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

Comments

  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

Post a Comment

Popular posts from this blog

6 guaranteed steps how to create CRON JOB FUNTION in wordpress

Create Cron Job function in Wordpress plugin Step 1: Register function on plugin activate  register_activation_hook(__FILE__, 'activate_one'); Step 2: add_filter function for interval //Filter for Adding multiple intervals add_filter( 'cron_schedules', 'intervals_schedule' ); // define interval function   function intervals_schedule($schedules) {   $schedules['everyminute'] = array(    'interval' => 60, // Every 1 minutes    'display'  => __( 'Every 1 minutes' )   );   return $schedules;  } Step 3: Activate hook function   //Schedule a first action if it's not already scheduled   function activate_one() {   if (!wp_next_scheduled('wpyog_cron_action')) {    wp_schedule_event( time(), 'everyminute', 'wpyog_cron_action');   }  } Step 4: Cron hook function   //Hook into that action that'll fire after 1 minutes   add_action('wpyog_cron_action', 'execute_

How to Create a jQuery Autocomplete in Wordpress

How to Create a jquery-ui Autocomplete in wordpress. Autocomplete provides suggestions while you type into the text field. In Wordpress we fetch dynamically matched pattern. Include javascript and css files in header. Create action inside functions.php or inside plugin code. add_action('wp_head', 'custom_register_scripts'); function custom_register_scripts(){ wp_register_style( 'techsudhir_jquery_ui_css', plugin_dir_url(__FILE__) . 'css/jquery-ui.css', false,'1.0.0' ); wp_enqueue_style( 'techsudhir_jquery_ui_css' ); wp_register_script('techsudhir_jquery_ui_js',plugin_dir_url(__FILE__) . 'js/jquery-ui.js',array('jquery'),'1.1', false); wp_enqueue_script('techsudhir_jquery_ui_js'); wp_localize_script( 'techsudhir_autocomplete', 'jqueryAutocomplete', array( 'ajaxurl' => admin_url( 'admin-ajax.php' ) ) ); wp_enqueue_script( 'techsudhir_

Facebook Login

Step 1: Go on Apps & create new Apps Step 2: Enter the app name & choose category Step 3: Enter the site domain Script code <div id="fb-root"></div> <script   src="http://connect.facebook.net/en_US/all.js#appId=<?php echo '603609636351597';?>"> </script> <script>    FB.init({              appId:'<?php echo '603609636351597';?>', cookie:true,              status:true, xfbml:true           });   FB.getLoginStatus(function(response) {   }); Login function call on click function faceLogin(){  FB.login(function(response) {    if (response.authResponse) {  FB.api('/me', function(response) { $('input[id=facebook_login_fb]').val(response['id']); $('#fb_login_form').submit();  });    } else {  console.log('User cancelled login or did