STORED PROCEDURE IN SQL SERVER

In this article, we learn about stored procedures and functions.

STORED PROCEDURE

• Stored procedures return a zero or N value.
• The procedure can work with insert, update, delete, and select statement.
• We can use functions inside the stored procedure.
• Return keyword used to exit the procedure.

Example:-

• In this example, we create a stored procedure by using the update command and finally we call the procedure using the exec command.

OLD TABLE

CREATE PROCEDURE output

AS

Begin

UPDATE [dbo].[tbl]

SET [name]=’ANKITA’

WHERE id=1

End

Output: To execute a stored procedure, we use the EXECUTE or EXEC the statement followed by the name of the stored procedure:

exec output

select * from [dbo].[tbl]

RESULT

PARAMETER OF STORED PROCDURE

INPUT PARAMETER:-

• The input parameter is a parameter whose value is passed into a stored procedure.
• The value of an IN parameter is a constant; it can’t be changed or reassigned within the module.

OUTPUT PARAMETER:-

• The output parameter is a parameter whose value is passed out of the stored procedure, back to the calling PL/SQL block.
• An OUT parameter must be a variable, not a constant. we cannot assign a default value to an OUT parameter outside of the module’s body.
• Syntax: parameter_name data_type OUTPUT
• To call a stored procedure with output parameters first we declare variables to hold the values returned by the output parameters. And then use these variables in the stored procedure call.

RETURN PARAMETER:-

• When we execute a stored procedure, it always returns an integer status usually, zero indicates success, and non-zero indicates the failure.
• We can have multiple output parameters in the stored procedure but only one return parameter.

PARAMETER

• To see the result we execute the stored procedure from the object explorer in the SQL server management studio.
• We cannot return values other than an integer value also we cannot return more than one value. But these two can possible with output parameters.

DELETE STORED PROCEDURE

• To delete a stored procedure, we use the DROP PROCEDURE or DROP PROC statement:

Syntax:-

DROP PROCEDURE sp_name;

OR

DROP PROC sp_name;

ADVANTAGES OF STORED PROCEDURE

• It reduces network traffic and provides better security to the data.
• Performance is increased because it taken a plan from a cache and not is recreated again n again.
• Centralizing code minimizes the maintenance we make changes at one place it gets reflected all over the application.

DISADVANTAGES OF STORED PROCEDURE

• The only disadvantage of a Stored Procedure is that it can be executed only in the database and utilizes more memory in the database server.

If you are newer to database learning -SQL Server recommended is the following must-watch video: -