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 w3wiki.
Query:
CREATE DATABASE w3wiki;
Output:
Step 2:To use the w3wiki database use the below command.
Query:
USE w3wiki
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: