laika222
4/30/2017 - 3:46 PM

Basic CREATE PROCEDURE, Creates Stored Procedure MS.mysql

CREATE PROCEDURE lsp_first_procedure
@firstnumber INT = 1,
@secondnumber INT = 3,
@thirdnumber INT = NULL

AS
  SET @thirdnumber = @firstnumber * @secondnumber;
  SELECT @thirdnumber + 2 AS 'Result';
GO

EXEC lsp_first_procedure 
  @firstnumber = 3, 
  @secondnumber = 7
  
  
-- DETAIL!!!

-- Create procedure called lsp_first_procedure. Note that unlike MySQL, you don't need to change the delimiter since you can execute the entire block code at the same time.
CREATE PROCEDURE lsp_first_procedure

-- Creates variable @firstnumber as integer, default value of 1 (this value can be updated later in the procedure)
@firstnumber INT = 1,

-- Creates variable @secondnumber as integer, default value of 3 (this value can be updated later in the procedure)
@secondnumber INT = 3,

-- Creates variable @thirdnumber as integer, default value of NULL (this value can be updated later in the procedure)
@thirdnumber INT = NULL

-- AS begins the core of the procedure which consists of two separate queries - in this case, adjusting the thirdnumber variable based on the vales of the @firstnumber and @secondnumber variables, and then in the second query adding 2 to @thirdnumber and displaying the results to the user
AS
  SET @thirdnumber = @firstnumber * @secondnumber;
  SELECT @thirdnumber + 2 AS 'Result';
  
-- GO ends the procedure
GO

-- Users uses EXEC (or EXECUTE) to run the procedure
EXEC lsp_first_procedure 

-- Plugs in value of 3 for @firstnumber and 7 for @secondnumber variables. If these values aren't plugged in here (i.e. these lines are omitted when executing the procedure), the variables will enter the procedure with their default values of 1 and 3 respectively.
  @firstnumber = 3, 
  @secondnumber = 7
DELIMITER
DROP PROCEDURE IF EXISTS first_procedure $
CREATE PROCEDURE first_procedure(p_firstnumber INT, p_secondnumber INT)

BEGIN

DECLARE firstnumber INT;
DECLARE secondnumber INT;
DECLARE thirdnumber INT;
SET firstnumber = p_firstnumber; 
SET secondnumber = p_secondnumber;

  SET thirdnumber = firstnumber * secondnumber;
  SELECT thirdnumber + 2 AS 'Result';

END $

DELIMITER ;

CALL first_procedure(2,3) $

-- DETAIL!!!

-- Set delimiter to $ so you can set up procedure with usual ; delimiter
DELIMITER $

-- Drop the procedure if it already exists, helpful when building and editing the procedure
DROP PROCEDURE IF EXISTS first_procedure $

-- Create the procedure first_procedure with two parameters, p_first_number and p_second number
CREATE PROCEDURE first_procedure(p_firstnumber INT, p_secondnumber INT)


-- Begin the procedure that will be completed each time
BEGIN


-- Declare the variables in the procedure (variables must be declared after the BEGIN keyword), and set them to the values entered by the user through the parameters
DECLARE firstnumber INT;
DECLARE secondnumber INT;
DECLARE thirdnumber INT;
SET firstnumber = p_firstnumber; 
SET secondnumber = p_secondnumber;

-- Begin the core of the procedure which consists of two separate queries - in this case, adjusting the thirdnumber variable based on the vales of the firstnumber and secondnumber variables, and then in the second query adding 2 to thirdnumber and displaying the results to the user
  SET thirdnumber = firstnumber * secondnumber;
  SELECT thirdnumber + 2 AS 'Result';

-- End the procedure
END $

-- Set the delimiter back to ;
DELIMITER ;

-- Call (execute) the procedure, plugging in the values 2 for p_firstnumber and 3 for p_secondnumber. The result is 8.
CALL first_procedure(2,3) ;