SQL Server Stored Procedures

• A stored procedure is a precompiled set of T-SQL statements stored in a database for repeated use, similar to subroutines or functions.

Anatomy of a Stored Procedure

• Header: Defines the procedure's name, parameters, and processing options, acting as an API or declaration.
• Body: Contains the T-SQL statements executed at runtime.

Creating Stored Procedures

• The basic syntax for creating stored procedures in SQL Server is as follows:
  • CREATE PROCEDURE procedure_name [{@parameter data_type} [= default] [OUTPUT] ] [,...n] AS sql_statement [...n]
• Example stored procedure to get equipment by make:

create procedure pr_equipment_get
      @make varchar(50)
 as
begin
      select *
      from tbl_equipment
      where make = @make
end

• Stored procedures can be modified by either dropping and recreating them or using the ALTER PROCEDURE statement.
• Using ALTER PROCEDURE is preferable to avoid undesirable effects on permissions and dependent objects.
• Example of dropping and recreating a stored procedure in SQL Server:

Drop Procedure pr_EquipmentByEqTypeID_Get
go
Create Procedure pr_EquipmentByEqTypeID_Get
     @intEqTypeId int
as
     Select *
     from tbl_equipment
     where EqTypeId = @intEqTypeId
GO

• Example of creating or replacing a stored procedure in MySQL:

DELIMITER $$
CREATE OR REPLACE PROCEDURE pr_getEquipment(_id INT)
BEGIN
  	Select *
     	from tbl_equipment
     	where EqTypeId = _id; 
END $$
DELIMITER ;

• Alternative method in SQL Server to drop a stored procedure if it exists:

IF object_id('pr_equipment_get') is not null
begin 
    drop PROCEDURE pr_equipment_get
end

Altering Stored Procedures

• The ALTER PROCEDURE statement modifies an existing stored procedure while preserving permissions and dependent objects.
• Syntax for altering a stored procedure is similar to creating one, but uses the ALTER keyword.
• Example of altering a stored procedure in SQL Server:

alter procedure pr_Equipment_Get 
      @make varchar(50)
as
	Select *
      	from  tbl_Equipment
      	where Make = @make
go

• Using ALTER PROCEDURE is better than dropping and recreating because it maintains the object identification number (id column) and references.

Limits of Stored Procedures

• Maximum identifier length (procedure name): 128 characters.
• Maximum number of input and output parameters: 2,100.
• Maximum size of the stored procedure body: 128MB.

Functionality of Stored Procedures

• Stored procedures can:
  • Return information to the caller.
  • Modify data in databases.
  • Implement business logic in the data tier.
  • Control access to data.
  • Improve system performance.
  • Reduce network traffic.
  • Perform other actions (e.g., email, OS commands, SQL Server object management).

Ways to Receive Information from a Stored Procedure

• Returning result sets
• Using input and output parameters
• Using return values
• Global cursor

Returning Result Sets

• To return a result set, include a T-SQL statement that returns a result set (e.g., a SELECT statement).
• A stored procedure can return multiple result sets using multiple SELECT statements.
• Some client data-access methods may only access the first result set or report an error.

Using Input and Output Parameters

• Example: Stored procedure using input parameters to filter equipment by make and model:

Create procedure pr_EqIdByMakeModel_List
     @make varchar(50),
     @model varchar(50)
as
     select EqId
     from tbl_Equipment
     where Make = @make and Model = @model
GO

• Execute a stored procedure with input parameters:

Execute pr_EqIdByMakeModel_List 'Toshiba', 'Portege 7020CT'

• Example using output parameter in SQL Server:

create procedure pr_Get_ID
	@name varchar(1000), --parameter
	@id int OUTPUT
as
begin
	select  @id = x_id from Table_1
	where x_name = @name
end

- -in order to use our output parameter, we must declare a variable to store the value from OUTPUT variable
declare @IDX int -- declare our variable for storing value
exec  pr_Get_ID 'MAMON', @IDX OUTPUT -- we use @IDX to store the value of our OUTPUT var
select @IDX -- return the value of our @IDX

• Example using output parameter in MySQL:

drop   procedure pr_selTable2;
delimiter $$
create procedure pr_selTable2(_id int,OUT _nme varchar(50))
	begin
		select  x_nme into _nme from tbl_X
		where x_id = _id;
	end $$
delimiter ;

call pr_selTable2 (1,@pangalan);
select @pangalan;

• Example using inout parameter in MySQL:

drop   procedure pr_selTable3;
delimiter $$
create procedure pr_selTable3(_id int, INOUT _nme varchar(50))
	begin
		select  x_nme into _nme from tbl_X
		where x_id = _id;
      	set _nme = concat('hi',' ',_nme);
	end $$
 delimiter ;

call pr_selTable3 (2,@pangalan);
select @pangalan;

Using Return Values

• Stored procedures can return an integer value to the caller using the RETURN statement.
• If no return value is specified, the default is zero (0).
• Example: SQL Server stored procedure returning EquipmentId:

Create Procedure  pr_EqIdByMakeModel_List_3 
     @make varchar(50),
     @model varchar(50)
as
Declare @intEqId int

Select @intEqId  = EquipmentId
from  tbl_equipment
where Make = @make and Model = @model
Return @intEqId

• Executing a stored procedure and accessing the returned value:

Declare @intEqId int
Execute @intEqId = pr_EqIdByMakeModel_List_3 'Toshiba', 'Portege 7020CT'
Select @intEqId 'Equipment Identifier'

• Example returning an error code using @@error:

Create Procedure pr_EqIdByMakeModel_List_5
     @make varchar(50),
     @model varchar(50),
     @intEqId int output
as
     select @intEqId = EqId
     from  tbl_equipment
     where Make = @make and Model = @model
Return @@error

• Executing the above stored procedure:

Declare   @intEqId int,
     @intErrorCode int
Execute @intErrorCode = pr_EqIdByMakeModel_List_5 'Toshiba','Portege 7020CT', @intEqId 
Select @intEqId result, @intErrorCode ErrorCode

• No equivalent code to Returning Values in MySQL

Default Values

• Parameters can have default values assigned using the = operator.
• Example: Stored procedure with default parameter values:

create Procedure pr_EqIdByMakeModel_List_6
     @make  varchar(50) = '%', @model varchar(50) = '%'
as
     Select *
     from  tbl_equipment
     where Make Like @make and Model Like @model

• If a parameter is omitted during execution, the default value is used.

Passing Parameters by Name

• Parameters can be passed by name instead of position, improving code readability.
• Syntax: @parameter_name = value.
• Example:

Execute  ap_EqIdByMakeModel_List_6 @Model = 'T%'

• Passing parameters by name can make code more maintainable.

Types of Stored Procedures

• User-defined
• System
• Extended
• Temporary
• Global temporary
• Remote
• CLR

Stored Procedure Basics

• CREATE PROCEDURE: Used to create a stored procedure.
• EXEC or CALL: Executes a stored procedure.
• IF OBJECT_ID(...) IS NOT NULL: Checks if a procedure already exists.
• DROP PROCEDURE: Deletes an existing procedure.
• GO: Marks the end of a batch (SQL Server only).

Parameters in Stored Procedures

• Input Parameters: Declared using @parameter_name (SQL Server) or _parameter_name (MySQL).
• Output Parameters: Defined with the OUTPUT keyword for returning values.

Data Manipulation (CRUD Operations)

• INSERT INTO: Adds new records.
• UPDATE: Modifies existing records.
• DELETE: Removes records.
• SELECT: Retrieves data.
• Example (SQL Server):

CREATE PROCEDURE pr_ManageEmployee
    @opt INT,           -- Operation type (1 = Insert, 2 = Update, etc.)
    @nme VARCHAR(500),  
    @sal DECIMAL(12,4), 
    @bonus DECIMAL(12,4), 
    @id INT
AS
BEGIN
    -- INSERT Operation
    IF @opt = 1
    BEGIN
        INSERT INTO tbl_Employee (NAME_, SALARY, BONUS)
        VALUES (@nme, @sal, @bonus);
    END

    -- UPDATE Operation
    IF @opt = 2
    BEGIN
        UPDATE tbl_Employee
        SET NAME_ = @nme, SALARY = @sal, BONUS = @bonus
        WHERE ID = @id;
    END

    -- DELETE Operation
    IF @opt = 3
    BEGIN
        DELETE FROM tbl_Employee
        WHERE ID = @id;
    END

    -- SELECT Operation
    IF @opt = 4
    BEGIN
        SELECT * FROM tbl_Employee;
    END
END

Using @@ROWCOUNT (SQL Server)

• @@ROWCOUNT: Returns the number of rows affected by the previous statement, useful for checking success/failure of queries.
• Example:

UPDATE tbl_Employee SET SALARY = @amt WHERE ID = @id;
IF @@ROWCOUNT > 0
    PRINT 'Record updated successfully';

Using Loops and Temporary Tables and Table Variables in Stored Procedures

• Looping structures allow a single command, or a group of statements, to be executed repeatedly.
• When using the T-SQL WHILE loop, a Boolean condition is checked every time the code within the loop is about to start.
• If the condition is true, the loop is executed.
• If not, control passes to the statement following the loop.
• WHILE loops are commonly used with cursors to process a set of data one row at a time.

WHILE Loops

• (SQL Server)

WHILE condition statement

• (MySQL)

WHILE search_condition DO
	statement_list
END WHILE

• Take note that the statement element can contain multiple commands if they appear between BEGIN and END commands.
• (SQL Server)

DECLARE @Iteration INT
SET @Iteration = 0
WHILE @Iteration< 10
BEGIN
	PRINT @Iteration
	SET @Iteration = @Iteration + 1
END

• Output:

0
1
2
3
4
5
6
7
8
9

• (MySQL)

 delimiter $$
   	CREATE PROCEDURE  dowhile()
   	BEGIN
   	 DECLARE v1 INT ;
   	 SET v1 = 0;
 
   	WHILE v1  10 THEN
                               LEAVE  loop_label;
                           END  IF;
                           SET  x = x + 1;
                           IF  (x mod 2) THEN
                               ITERATE  loop_label;
                           ELSE
                               SET  str = CONCAT(str,x,',');
                           END  IF;
 
               END LOOP;    
               SELECT str;
   	END$$
 DELIMITER ;

Exiting a Loop Explicitly

• Sometimes you will wish to terminate a loop even when the condition part is true.
• This can be achieved using the BREAK command on SQL Server.
• This command specifies that the control should pass to the command immediately following the current loop.
• Example

DECLARE @ToSquare INT
DECLARE @Square INT
SET @ToSquare = 0
WHILE @ToSquare < 10
BEGIN
	SET @ToSquare = @ToSquare + 1
	SET @Square = @ToSquare * @ToSquare
	IF @Square > 200
    	BREAK
	PRINT @Square
END

• Output:

1
4
9
16
25
36
49
64
81
100

• In MySQL, LEAVE is used to exit the flow control construct that has the given label.
• If the label is for the outermost stored program block, LEAVE exits the program.
• In order to use LEAVE, we must place our loop or condition inside a label to tell our code where to exit.
• MYSQL Example

delimiter $$
CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;
   	 myLoop: WHILE v1 > 0 DO
         	SET v1 = v1 - 1;
                	if v1 < 3 then
                      	leave myLoop;
                	end if;
   	  END WHILE myLoop;
 
   	select v1;
END $$
 
delimiter ;

Restarting a Loop

• If you wish to terminate a single iteration of a loop you can use the CONTINUE command in SQL Server.
• This command immediately stops the current iteration and rechecks the loop's condition.
• If the condition is still true, the loop is restarted.

//SQL Server
DECLARE @ToSquare INT
DECLARE @Square INT
SET @ToSquare = 0
WHILE @ToSquare < 100
BEGIN
	SET @ToSquare = @ToSquare + 1
	SET @Square = @ToSquare * @ToSquare
	IF @Square < 100
    	CONTINUE
	IF @Square > 200
    	BREAK
	PRINT @Square
END

• In MySQL, The ITERATE statement allows you to skip the entire code under it and start a new iteration.
• The ITERATE statement is similar to the continue statement in PHP, C/C++, Java, etc. ITERATE means “start the loop again.”.
• MYSQL Example

delimiter $$
CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 0;
   	DECLARE str  VARCHAR(255);
   	 SET str =  '';
   	 myLoop: WHILE v1  10 then
                      	leave myLoop;
                	end if;
 
                	SET v1 = v1 + 1;
 
                	IF  (v1 mod 2) THEN
                      	ITERATE  myLoop;
                	ELSE
                      	SET  str = CONCAT(str,v1,',');
                	END  IF;
                      	
   	  END WHILE myLoop;
 
   	select str;
END $$
 
delimiter ;

Nesting

• Loops can be nested to provide more complex looping structures.
• When nesting loops, BREAK and CONTINUE commands affect only the loop that they appear within.
• i.e. when breaking out of an inner loop, control passes to the next statement after the inner loop, which will be within the outer loop.

//SQL Server
DECLARE @Val1 INT
DECLARE @Val2 INT
SET @Val1 = 1
WHILE @Val1< 3
BEGIN
	SET @Val2 = 1
	WHILE @Val2< 3
 	BEGIN
    		PRINT CONCAT(@Val1,' ',@Val2)
    		SET @Val2 = @Val2 + 1
 	END
	SET @Val1 = @Val1 + 1
END