Open In App

How to Execute SQL Server Stored Procedure in SQL Developer?

Last Updated : 23 Sep, 2021
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

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:



Similar Reads

How to SQL Select from Stored Procedure using SQL Server?
There may be situations in SQL Server where you need to use a stored procedure to get data from a SQL query. For direct data selection from a stored procedure within a query, SQL Server offers options like OPENQUERY and OPENROWSET. The usual way is running the stored procedure independently and then querying the outcomes. The idea of utilizing SQL
3 min read
Use of Single Quotes for Stored Procedure Parameters in SQL Server
Whenever any value is passed to the variable or column of character data type, the string value has single quotes('') around them otherwise it will through an error. Below we will discuss this feature (Use of Single Quotes for Stored Procedure Parameters in SQL Server) of SQL Server. Example 1: DECLARE @inp VARCHAR(100) SET @inp = 'GeeksforGeeks' S
2 min read
How to Modify a Stored Procedure in SQL Server?
In this article, we will learn to modify the created stored procedure in MS SQL.You can modify the Stored Procedure in two ways. one is by using a client called SSMS and other way is by using T-SQL statements + SSMS in MS SQL Server. Method 1: Using SQL Server Management Studio (SSMS) to Modify the Stored ProcedureIn Object Explorer, connect to an
3 min read
How to Export Data to the .CSV File Using SQL Server Stored Procedure?
Exporting data from SQL Server to a CSV file is a common task when handling large datasets or sharing data with other applications. SQL Server Management Studio (SSMS) provides a straightforward way to export tables using its Import and Export Wizard. In this article, we will see, the process of exporting Data to the .CSV file using the SQL Server
3 min read
How to Create and Call a Stored Procedure in SQL?
With this article, we will learn how to Create and Call a Stored Procedure in SQL. For this article, we are going to use MSSQL as our database server. What is a Stored Procedure?A stored procedure is a pre-written SQL query that can be called multiple times and will run as the same. Like we can create a Stored procedure for Insert, select, update i
2 min read
SQL Query to Rename Stored Procedure
Stored Procedure is a saved SQL code. It is created to save time as we can use it again and again without writing the whole query. In this article, we will see how to Rename a Stored Procedure. To create a Procedure:Syntax: CREATE PROCEDURE procedure_name AS SELECT * FROM table_name;SQL query whose procedure needs to be created is always written af
2 min read
Stored Procedures classification based on Input and Output Parameters in SQL Server
Stored Procedure: The stored procedure has the following key points as follows. It is a collection of SQL statements such as if you want to write and read data from a database then you can use create a statement for write and select statement for reading the data from database and SQL command logic for the same, which is compiled and stored on the
2 min read
Basic Query in PL/SQL procedure
PL/SQL (Procedural Language/Structured Query Language) is a powerful extension to SQL, designed to combine the robustness of SQL with procedural constructs like loops, conditions, and more. It plays a crucial role in writing complex database interactions in Oracle databases. This article will cover an overview of PL/SQL, basic query operations, and
3 min read
Advantages and Disadvantages of Using Stored Procedures - SQL
A Stored Procedure is a type of code in SQL that can be stored for later use and can be used many times. So, whenever you need to execute the query, instead of calling it you can just call the stored procedure. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter values that is passed. Synt
3 min read
Some Useful System Stored Procedures in SQL
Some Useful System Stored Procedures in SQL. These procedures are built-in procedures and will help to extract the definition and dependencies of the existing user-defined stored procedures. sp_help : This will display the Stored procedure Name, Schema Name, created date, and Time or if there are any parameters, then Parameter Name, Data Type, Leng
2 min read
SQL VS ADO.NET With Stored Procedures
Stored Procedures have been used as a solution for so many problems, performance issues and not being able to separate the business Logic and transaction management. Stored Procedure tries to solve this major problem while developing a website. In .NET when we want to create a website or desktop application and when we have to query the database we
6 min read
SQL Stored Procedures
A stored procedure in SQL is a group of SQL queries that can be saved and reused multiple times. It is very useful as it reduces the need for rewriting SQL queries. It enhances efficiency, reusability, and security in database management. Users can also pass parameters to stored procedures so that the stored procedure can act on the passed paramete
4 min read
How to Execute SQL File with Java using File and IO Streams?
In many cases, we often find the need to execute SQL commands on a database using JDBC to load raw data. While there are command-line or GUI interfaces provided by the database vendor, sometimes we may need to manage the database command execution using external software. In this article, we will learn how to execute an SQL file containing thousand
5 min read
How to execute an SQL query and fetch results using PHP ?
In this article, we will discuss how to execute an SQL query and how to fetch its result? We can perform a query against the database using the PHP mysqli_query() method. Syntax: We can use the mysqli_query( ) method in two ways: Object-oriented styleProcedural style Parameters: connection: It is required that specifies the connection to use.query:
3 min read
Configure SQL Jobs in SQL Server using T-SQL
In this article, we will learn how to configure SQL jobs in SQL Server using T-SQL. Also, we will discuss the parameters of SQL jobs in SQL Server using T-SQL in detail. Let's discuss it one by one. Introduction :SQL Server Agent is a component used for database task automation. For Example, If we need to perform index maintenance on Production ser
7 min read
Difference Between Trigger and Procedure in DBMS
In Relational Database Management Systems (RDBMS), triggers and procedures are two important elements necessary for data manipulation. Still, there are vast differences between them, although many individuals cannot tell the difference because their uses are quite similar. On the other hand, trigger are actions that are fired in response to some sp
5 min read
MySQL | Creating stored function
The CREATE FUNCTION statement is used for creating a stored function and user-defined functions. A stored function is a set of SQL statements that perform some operation and return a single value. Just like Mysql in-built function, it can be called from within a Mysql statement. By default, the stored function is associated with the default databas
2 min read
How to Execute a Second Query if the First One Returns Nothing?
In certain database operations, we may need to execute a query only if another query returns no results. This conditional execution can be crucial in maintaining data integrity, avoiding duplicates, or performing specific tasks when data is missing. In this article, we’ll explore how to achieve this using SQL with practical examples and show the ou
5 min read
SQL SERVER – Input and Output Parameter For Dynamic SQL
An Input Parameter can influence the subset of rows it returns from a select statement within it. A calling script can get the value of an output parameter. An aggregate function or any computational expression within the stored process can be used to determine the value of the output parameter. A parameter whose value is given into a stored proced
3 min read
SQL - SELECT from Multiple Tables with MS SQL Server
In SQL we can retrieve data from multiple tables also by using SELECT with multiple tables which actually results in CROSS JOIN of all the tables. The resulting table occurring from CROSS JOIN of two contains all the row combinations of the 2nd table which is a Cartesian product of tables. If we consider table1 contains m rows and table2 contains n
3 min read
SQL Query to Convert Rows to Columns in SQL Server
In this article we will see, how to convert Rows to Column in SQL Server. In a table where many columns have the have same data for many entries in the table, it is advisable to convert the rows to column. This will help to reduce the table and make the table more readable. For example, Suppose we have a table given below: NAMECOLLEGEROLL NUMBERSUB
2 min read
BULK INSERT in SQL Server(T-SQL command)
BULK INSERT in SQL Server(T-SQL command): In this article, we will cover bulk insert data from csv file using the T-SQL command in the SQL server and the way it is more useful and more convenient to perform such kind of operations. Let's discuss it one by one.  ConditionSometimes there is a scenario when we have to perform bulk insert data from .cs
3 min read
SQL Server | Convert Tables in T-SQL into XML
XML (Extensible Markup Language) is a widely-used markup language designed to store and transfer structured data between different systems and platforms. While HTML focuses on the visual representation of data OverviewXML is similar to HTML which is designed to structure and store data for sharing across different systems and platforms.Unlike HTML,
3 min read
Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
Structured Query Language (SQL): Structured Query Language (SQL) has a specific design motive for defining, accessing and changement of data. It is considered as non-procedural, In that case the important elements and its results are first specified without taking care of the how they are computed. It is implemented over the database which is drive
2 min read
QUOTENAME() Function in SQL Server
QUOTENAME() function : This function in SQL Server is used to return a Unicode string with delimiters added in order to make the string a valid SQL Server delimited identifier. Features : This function is used to find a Unicode string with delimiters added. This function accepts only strings and delimiters. This function add delimiters by default i
3 min read
SQL Server Identity
Identity column of a table is a column whose value increases automatically. The value in an identity column is created by the server. A user generally cannot insert a value into an identity column. Identity column can be used to uniquely identify the rows in the table. Syntax: IDENTITY [( seed, increment)] Seed: Starting value of a column. Default
1 min read
SQL SERVER | Conditional Statements
While loop: In SQL SERVER, while loop can be used in similar manner as any other programming language. A while loop will check the condition first and then execute the block of SQL Statements within it as long as the condition evaluates true. Syntax: WHILE condition BEGIN {...statements...} END; Parameters: 1. Condition: The condition is tested in
2 min read
SQL Server | SERVERPROPERTY()
SQL Server provides a System Defined function SERVERPROPERTY(propertyname). SERVERPROPERTY(): SERVERPROPERTY() function is used to return the information about different properties of system or so called the instance information. propertyname: This expression contains the information about property in question and returns the same. Following are fe
2 min read
Mean and Mode in SQL Server
Mean is the average of the given data set calculated by dividing the total sum by the number of values in data set. Example: Input: 1, 2, 3, 4, 5 Output: 3 Explanation: sum = 1 + 2 + 3 + 4 + 5 = 15 number of values = 5 mean = 15 / 5 = 3 Query to find mean in the table SELECT Avg(Column_Name) FROM Table_Name Example: Creating Table: Table Content: Q
1 min read
Allow only alphabets in column in SQL Server
There may occur some situations when data in column should only be in alphabets, for example: Name column in details table. If user try to input other than alphabets, it will give error. To allow only alphabets in a column check constraint can be applied over column. To get information about Constraints and Check Constraints, refer below article li
1 min read
three90RightbarBannerImg