How to Execute SQL Server Stored Procedure in SQL Developer?

A stored procedure is a set of (T-SQL ) statements needed in times when we are having the repetitive usage of the same query. When there is a need to use a large query multiple times we can create a stored procedure once and execute the same wherever needed instead of writing the whole query again.

In this article let us see how to execute SQL Server Stored Procedure in MSSQL.

Syntax: For creating a stored procedure

CREATE PROCEDURE (or CREATE PROC) proc_name
AS
BEGIN
  QUERY
END

Step 1: We creating a Database. For this use the below command to create a database named GeeksforGeeks.

Query:

CREATE DATABASE GeeksforGeeks;

Output:

Step 2:To use the GeeksforGeeks database use the below command.

Query:

USE GeeksforGeeks

Output:

Step 3:Now we creating a table. Create a table student_details with  3 columns using the following SQL query.

Query:

CREATE TABLE student_details(
   stu_id VARCHAR(8),
   stu_name VARCHAR(20),
   stu_cgpa DECIMAL(4,2) );

Output:

Step 4: The query for Inserting rows into the Table. Inserting rows into student_details table using the following SQL query.

Query:

INSERT INTO student_details VALUES('40001','PRADEEP',9.6);
INSERT INTO student_details VALUES('40002','ASHOK',8.2);
INSERT INTO student_details VALUES('40003','PAVAN KUMAR',7.6);
INSERT INTO student_details VALUES('40004','NIKHIL',8.2);
INSERT INTO student_details VALUES('40005','RAHUL',7.0);

Output:

 Step 5: Viewing the inserted data

Query:

SELECT * FROM student_details;

Output:

• Query to create a stored procedure to view the table:

Query:

CREATE PROCEDURE view_details
AS
BEGIN 
  SELECT * FROM student_details;
END

Output:

 For executing a stored procedure we use the below syntax:

Syntax:

 EXEC proc_name
 or
 EXECUTE proc_name
 or 
 proc_name

Query:

EXECUTE view_details

Output:

• Query to create a stored procedure that takes the argument as stu_id and displays the cgpa of that id.

Query:

CREATE PROCEDURE get_student_cg_details
@stu_id VARCHAR(20)
AS
BEGIN  
 SELECT stu_id, stu_cgpa FROM student_details
 WHERE stu_id= @stu_id
END

Output:

Query:

EXECUTE get_student_cg_details '40002'

Output:

L

lokeshpotta20

Follow

Improve