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