The procedure is SQL object which contains a series of T-SQL statements as function. Procedures are created to execute single or multiple DML operations in SQL DBMS. Procedures that accept arguments in the form of parameters & execute operations by returning values or voids. During the very first stored procedure call, SQL DBMS generates an execution plan & caches it in the server. In subsequent calls, the SQL server reuses the plan to run query very fast & reliable performance.

How to create SQL Procedure?

 Please refer below syntax for creating SQL Procedure :

CREATE { PROCEDURE | PROC } [SchemaName.]ProcedureName
[ @parameter [TypeSchemaName.] datatype
[ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ]
, @parameter [TypeSchemaName.] datatype
[ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ] ]
[ WITH { ENCRYPTION | RECOMPILE | EXECUTE AS Clause } ]
[ FOR REPLICATION ]
AS
BEGIN
[DeclarationSection]
ExecutableSection
END;

Let’s understand the meaning of Syntax definition :

• SchemaName: Defines schema name of stored procedure
• ProcedureName: Defines name for the stored procedure
• @parameter: Single or multiple parameters can be passed in a stored procedure
• TypeSchemaName: Defines type for schema if applicable
• Datatype: Defines type for @parameter.
• VARYING: This is for the cursor parameter when the result set is an output parameter.
• Default :  This determines default value assigned to @parameter.
• OUT: This determines @parameter is an output parameter.
• OUTPUT: This determines @parameter is an output parameter.
• READONLY: This determines @parameter can not be overwritten by the stored procedure.
• ENCRYPTION: This determines the source for the stored procedure will not be stored as plain text in the system views in SQL Server.
• RECOMPILE: This determines that a query plan will not be cached for this stored procedure.
• EXECUTE AS clause: It assigns the security context to execute the stored procedure.
• FOR REPLICATION: This determines the stored procedure is executed only during replication.

Example:

Let’s look at an example of how to create a stored procedure in SQL Server (Transact-SQL).

The following is a simple example of a procedure:

CREATE PROCEDURE FindURL
@url_name VARCHAR(50) OUT
AS
BEGIN
DECLARE @url_id INT;
SET @url_id = 8;
IF @url_id < 10
SET @url_name= 'google.com';
ELSE
SET @url_name= 'yahoo.co.in';
END;

This procedure is called FindURL. It has one parameter called @url_name which is an output parameter that gets updated based on the variable @url_id. You could then reference the new stored procedure called FindURL as follows:

USE [test]
GO
DECLARE @url_namevarchar(50);
EXEC FindURL @url_name OUT;
PRINT @site_name;
GO

How to call SQL procedure?

Once we have defined stored procedure, we can make a call to store procedure by using EXECUTE or EXEC command in SQL

Syntax:

Execute <Procedure Name> [ <Procedure parameters>]
Or
Execute <Procedure Name> [ <Procedure parameters>]

Procedure Parameters:  These will be procedure parameterized arguments to be passed in case of parameterized procedure call if the procedure is without parameters then we do not require to pass any parameter argument.

What is Drop SQL Procedure?

 SQL is facilizing you to delete or remove existing store procedure from SQL schema object using DROP Procedure command. Once the store procedure is dropped from schema object we can not restore it back. If you want to use store procedure again after dropping it from the schema object, we need to recreate it again in schema object by defining the procedure definition

How to DROP Procedure?

 Syntax

The syntax to a drop a stored procedure in SQL Server (Transact-SQL) is:

DROP PROCEDURE ProdcedureName;

ProdcedureName: The name of the stored procedure that you desire to drop

Example

Let’s look at an example of how to drop a stored procedure in SQL Server.

For example:

DROP PROCEDURE FindURL;

This DROP PROCEDURE example would drop the stored procedure called FindURL.

Let’s see a few more examples,

In the below example, we are going to convert T-SQL statement into store procedure . we are fetching “CourseName”,”” CourseSubject” & “CourseTopic” from “Course” table by passing values in where clause with variables @CourseName & @CourseSubject

SELECT CourseName,CourseSubject,CourseTopic
FROM Besant.Course
WHERE CourseName = @CourseName AND CourseSubject = @CourseSubject 
AND EndDate IS NULL; 

We are creating a stored procedure with the name “uspGetCourse” which is taking  @CourseName & @CourseSubject  as parameters in the procedure definition.

GO  
CREATE PROCEDURE Besant. uspGetCourse   
@CourseName nvarchar(50),   
@CourseSubject nvarchar(50)   
AS   
SET NOCOUNT ON;  
SELECT CourseName,CourseSubject,CourseTopic 
FROM Besant.Course 
WHERE CourseName = @CourseName AND CourseSubject = @CourseSubject  
AND EndDate IS NULL;  
GO

We are calling procedure by executing “Execute” or Exec command in SQL.

Execute command is taking Procedure name Besant.uspGetCourse as parameter & we are passing two more parameter arguments in the procedure call.

EXECUTE Besant.uspGetCourse N'Python', N'Regex'; 
-- Or 
EXEC Besant.uspGetCourse @CourseName = N'Python', @CourseSubject = N'Regex'; 
GO 
-- Or 
EXECUTE Besant.uspGetCourse @CourseName = N'Python', @CourseSubject = N'Regex'; 
GO

To drop/delete procedure from SQL schema object, we are executing following command  which will delete the procedure name “uspGetCourse”

DROP PROCEDURE Besant.uspGetCourse

Once we have deleted the procedure name “uspGetCourse” in SQL schema object then we cannot retrieve it back until and unless we create a procedure definition again in SQL schema object.

Related Blogs

1. SQL Joins
2. Schema in SQL
3. Decode in SQL
4. CASE Statement in MySQL
5. Normalization in SQL
6. SQL ORDER BY Clause
7. LIKE Operator in SQL
8. SQL Views
9. SQL Concatenate Function
10. What are the Manipulation Functions in SQL
11. Primary Key In SQL
12. SQL DateTime
13. SQL Functions