Procedure in mysql

Procedure

A database stored program   sometimes called a stored module or a stored routine is
a computer program (a series of instructions associated with a name) that is stored
within, and executes within, the database server.

you can Call Stored Programs from Stored Programs

Use a procedure if you need only to perform a computation to produce an effect or action without returning a value, or if the computation produces result sets (which a function is not allowed to do).

To create a stored function or procedure, use a CREATE FUNCTION or CREATE PROCEDURE statement .

CREATE PROCEDURE BookAmount (@bookId INT)
BEGIN
SELECT BookName, Quantity FROM Books WHERE BookID=@bookId;
END

there is one parameter definition:@bookId, which is defined as an INT type. If you do not need to include any parameter definitions, you must still include the parentheses. The actual SQL statement is enclosed in a BEGIN/END block. Notice that that SELECT statement is the only component of the stored procedure definition that ends with a semi-colon. You should terminate each SQL statement in the BEGIN/END block with a semi-colon.

Once you’ve created your stored procedure, you can invoke it simply by using the CALL statement:
CALL BookAmount(101);

The following procedure displays a greeting with your username, or “earthling” if you are an anonymous user:

CREATE PROCEDURE greetings ()
BEGIN
DECLARE user CHAR(77) CHARACTER SET utf8;
SET user = (SELECT CURRENT_USER());
IF INSTR(user,'@') > 0 THEN
SET user = SUBSTRING_INDEX(user,'@',1);
END IF;
IF user = '' THEN # anonymous user
SET user = 'earthling';
END IF;
SELECT CONCAT('Greetings, ',user, '!') AS greeting;
END;

---------------------------------------------------------------------------------------------------

CREATE PROCEDURE do_little ()
BEGIN
DO SLEEP(1);
END;

------------------------------------------------------------------------------------------------------

CREATE PROCEDURE do_nothing ()
BEGIN

END;

A stored procedure is similar to a stored function, but it doesn’t return a value.Therefore,
it does not have a RETURNS clause or any RETURN statements.The following simple
stored procedure is similar to the count_born_in_year() function, but instead of calculating
a count as a return value, it displays a result set containing a row of information for
each president born in the given year.

delimiter $
CREATE PROCEDURE show_born_in_year(p_year INT)
BEGIN
SELECT first_name, last_name, birth, death
FROM president
WHERE YEAR(birth) = p_year;
END$
delimiter ;

CALL show_born_in_year(1908);
-----------------------------------------------------------------------------------------------------------

CREATE PROCEDURE update_expiration (p_id INT UNSIGNED, p_date DATE)
BEGIN
UPDATE member SET expiration = p_date WHERE member_id = p_id;
END;

CALL update_expiration(61, CURDATE() + INTERVAL 1 YEAR);


Stored procedure parameters can have one of three types. For an IN parameter, the caller passes a value into the procedure.The value can be modified within the procedure, but
any changes are not visible to the caller after the procedure returns.

An OUT parameter is the opposite.The procedure assigns a value to the parameter, which can be accessed by the caller after the procedure returns.
An INOUT parameter enables the caller to pass in a value, and to get back a value
Parameters are IN by default if no type is given. To use an OUT or INOUT parameter, specify a variable name when you call the procedure.

The following procedure demonstrates use of OUT parameters. 

It counts the number of male and female students in the student table and returns the counts via its parameters so that the caller can access them:

CREATE PROCEDURE count_students_by_sex (OUT p_male INT, OUT p_female INT)
BEGIN
SELECT COUNT(*) FROM student WHERE sex = 'M' INTO p_male;
SELECT COUNT(*) FROM student WHERE sex = 'F' INTO p_female;
END;

The IN, OUT, and INOUT keywords do not apply to stored functions, triggers, or events.
For stored functions, all parameters are like IN parameters.Triggers and events do not
have parameters at all.

Stored functions calculate a value to be returned to the caller for use in expressions.
The following example creates a function that takes an integervalued parameter representing a year. (I use p_ as a prefix to distinguish parameter names from other names such as those of tables or columns.) The function uses a subquery to

determine how many presidents were born in that year and returns the count:

delimiter $
CREATE FUNCTION count_born_in_year(p_year INT)
RETURNS INT
BEGIN
RETURN (SELECT COUNT(*) FROM president WHERE YEAR(birth) = p_year);
END$
delimiter ;

SELECT count_born_in_year(1913);


Stored procedure that creates a temporary table

CREATE PROCEDURE sp_overdue_sales ( )
BEGIN
DROP TEMPORARY TABLE IF EXISTS overdue_sales_tmp;
CREATE TEMPORARY TABLE overdue_sales_tmp AS
SELECT sales_id,customer_id,sale_date,quantity,sale_value
FROM sales
WHERE sale_status='O';
END;


You should rely on dynamic SQL only when needed. It is more complex and less efficient than static SQL . SQL as a prepared statement; it will, in fact, execute any SQL that is passed in as an
argument.

Stored procedure with dynamic SQL

CREATE PROCEDURE execute_immediate(in_sql VARCHAR(4000))
BEGIN
SET @tmp_sql=in_sql;
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END;

------------------------------------------------------------------------------------------------------

Function will not compile due to the INOUT clause
CREATE FuNCTION f inout(IN0UT x INT) RETURNS INT
BEGIN
SET x=1;
RETURN (1) ;
END;

Stored function to return customer count for a sales rep
CREATE FuNCTIoN custoners-for-rep(in-rep-id INT)
RETURNS INT
READS SQL 0ATA
BEGIN
DECLARE customer count INT;
SELECT CoUNT(*)
INTO customer count
FROM customers
WHERE sales-rep-id=in-rep-id:
RETURN(customer count):
END:

-------------------------------------------------------------------------------------------

DELIMITER $
CREATE PROCEDURE pValidate(IN pUsername VARCHAR(100), IN pPassword VARCHAR(100), OUT pResult VARCHAR(100))
BEGIN
DECLARE count INT;
SELECT count(1) INTO count FROM logins WHERE username = pUsername AND password = pPassword;
IF count > 0 THEN
SET pResult = 'success';
ELSE
SET pResult = 'failed';
END IF;
END;
$
DELIMITER ;

SET @s = "";
CALL pValidate('test','test321', @s);
SELECT @s;
------------------------------------------------------------------------------------------------------
create function testing(@username varchar(10), @password varchar(10))
RETURNS bit
BEGIN
if EXISTS(select * from signupform where username = @username and password = @password)
return 1;
else
return 0;
end if;
end;

If EXISTS(SELECT TOP 1 * FROM signupform WHERE username = @username AND password = @password)
begin
set @res = 1;
end
select @res as valid
return

------------------------------------------------------------------------------------------------

CREATE PROCEDURE useOutputParameter ()
BEGIN
CALL setOutputParameter(@tmpOutputParameter);

IF @tmpOutputParameter = 'output'
THEN SELECT 'string: output was returned' AS res;
END IF;
END//

DELIMITER ;

------------------------------------------------------------------------------------

CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20)
BEGIN
DECLARE outtext VARCHAR(20);
SET outtext = 'Hello World';
RETURN outtext;
END

--------------------------------------------------------------------------------------------


CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20)
BEGIN
DECLARE output_text VARCHAR(20) DEFAULT 'HelloWorld';
RETURN output_text;
END

-----------------------------------------------------------------------------------------------------

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
# Set value of OUT parameter
SELECT VERSION() INTO ver_param;
# Increment value of INOUT parameter
SET incr_param = incr_param + 1;
END;

------------------------------------------------------------------------------------------------------
DELIMITER $
DROP PROCEDURE IF EXISTS `test`.`rc_exec`$
CREATE PROCEDURE `test`.`rc_exec` (IN command VarChar(60000))
BEGIN
SET @query = command;
PREPARE stmt FROM @query;
EXECUTE stmt;
SELECT ROW_COUNT() AS 'Affected rows';
END$
DELIMITER ;

 Simple stored function with multiple RETURN statements

CREATE FUNCTION cust_status(in_status CHAR(1))
RETURNS VARCHAR(20)
BEGIN
IF in_status = 'O' THEN
RETURN('Overdue');
ELSEIF in_status = 'U' THEN
RETURN('Up to date');
ELSEIF in_status = 'N' THEN
RETURN('New');
END IF;
END;

Stored functions cannot include OUT or INOUT parameters; if you need to return multiple variables from your stored program then a procedure is possibly more appropriate than a function

-----------------------------------------------------------------------------------

CREATE FUNCTION cust_status(IN in_status CHAR(1))
RETURNS VARCHAR(20)
BEGIN
DECLARE long_status VARCHAR(20);
IF in_status = 'O' THEN
SET long_status='Overdue';
ELSEIF in_status = 'U' THEN
SET long_status='Up to date';
ELSEIF in_status = 'N' THEN
SET long_status='New';
END IF;
RETURN(long_status);
END;

-------------------------------------------------------------------------------------------------------

SELECT CASE customer_status
WHEN 'U' THEN 'Up to Date'
WHEN 'N' THEN 'New'
WHEN 'O' THEN 'Overdue'
END as Status, count(*) as Count
FROM customers
GROUP BY customer_status
ORDER BY CASE customer_status
WHEN 'U' THEN 'Up to Date'
WHEN 'N' THEN 'New'
WHEN 'O' THEN 'Overdue'
END

--------------------------------------------------------------------------------

DELIMITER $
CREATE FUNCTION `fn_create_category_test` (test_arg varchar(50))
RETURNS INT
BEGIN
DECLARE new_id int;
set new_id=8;
return new_id;
END $
DELIMITER ;

Call function in C#

using(MySqlConnection conn = new MySqlConnection(connString))
{
MySqlCommand command = new MySqlCommand("spSomeProcedure;", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;

// Add your parameters here if you need them
command.Parameters.Add(new MySqlParameter("someParam", someParamValue));
conn.Open();
int result = (int)command.ExecuteScalar();
}

------------------------------------------------------------------------------------------------------

MySqlCommand cmd = new MySqlCommand("DeleteMessage", new MySqlConnection(GetConnectionString()));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("param1", MessageItem.Entry_ID));
cmd.Connection.Open();
int i = cmd.ExecuteNonQuery();
cmd.Connection.Close();