Get Started: SQL Stored Procedures – SQL Server

In This Article, You Will Know About SQL CASE Statement. 

SQL CASE Statement – Before moving ahead, let’s know a bit about the SQL CASE Statement.

Table of Contents

What is a Stored Procedure?

A SQL Store Procedure is a process of saving & storing a code to use it over and over again.

If you have any SQL query that you have to write more than once, then you can store that code to use later too.

It is possible to pass parameters to a stored procedure so that the stored procedure can act based on the parameter value(s) that is passed.

Syntax

				
					CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
				
			

Execute a Stored Procedure

				
					EXEC procedure_name;
				
			

Example Database

Table name: Data_1

S.No

Name

Roll_No

Section_ID

1

Alex

5

A

2

Rolex

6

B

3

Salex

7

C

4

Jalex

8

D

5

Kalex

9

E

Stored Procedure Example

Example: Use the SQL Stored Procedure to store a SQL query.

				
					CREATE PROCEDURE All_Names
AS
SELECT * FROM Data_1
GO;
				
			

As, a result, it selects data from table Data_1 and stores it as All_Names.

Example: Use the SQL EXEC to execute stored SQL queries.

				
					CREATE PROCEDURE All_Names
AS
SELECT * FROM Data_1
GO
EXEC All_Names;
				
			

Stored Procedure With One Parameter

Example: Use the SQL Stored Procedure to store a SQL query with a parameter.

				
					CREATE PROCEDURE All_Names @Name nvarcahar(7)
AS
SELECT * FROM Data_1 WHERE Name = @Name
GO
EXEC All_Names @Name = 'Alex';
				
			

As a result, it returns ‘Alex’ as the SQL query from the SQL Stored Procedure.

As the parameter passed for only the name ‘Alex’ in the whole “Name” column, therefore, it returned ‘Alex’ as its SQL query.

Stored Procedure With Multiple Parameters

Passing multiple parameters is very easy, just write all parameters together (one by one) and separate the data type of each parameter by a comma.

Example: Use the SQL Stored Procedure to store a SQL query with multiple parameters.

				
					CREATE PROCEDURE All_Names @Name nvarcahar(7), @Roll_No nvarcahar(7)
AS
SELECT * FROM Data_1 WHERE Name = @Name AND Roll_No = @Roll_No
GO
EXEC All_Names @Name = 'Alex', @Roll_No = 5;

				
			

If you find anything incorrect in the above-discussed topic and have further questions, please comment below.

Connect on:

Leave a Comment

Stay in the loop

codingstreets