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 


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  [ ]
Or
Execute  [ ]
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.
  Click Here – Get Prepared for SQL Interviews 
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.
  Click Here – Enroll Now! 
Related Blogs
SQL Joins
Schema in SQL
Decode in SQL
CASE Statement in MySQL
Normalization in SQL
SQL ORDER BY Clause
LIKE Operator in SQL
SQL Views
SQL Concatenate Function
What are the Manipulation Functions in SQL
Primary Key In SQL
SQL DateTime
SQL Functions
The post Procedures in SQL appeared first on Besant Technologies.