Scribd
Upload
65 views2 pages

SQL Server Stored Procedure Example

Stored procedures are reusable blocks of SQL code that can accept parameters. They are created using the CREATE PROCEDURE statement and executed with EXEC. Parameters allow values to be passed into a stored procedure. The example creates a stored procedure that takes a country parameter and returns customers matching that country from a Customers table.

Uploaded by

Fake Account
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
65 views2 pages

SQL Server Stored Procedure Example

Stored procedures are reusable blocks of SQL code that can accept parameters. They are created using the CREATE PROCEDURE statement and executed with EXEC. Parameters allow values to be passed into a stored procedure. The example creates a stored procedure that takes a country parameter and returns customers matching that country from a Customers table.

Uploaded by

Fake Account
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Stored procedures in SQL

Stored procedures are prepared SQL code that you save so you can
reuse it over and over again. So if you have an SQL query that you
write over and over again, save it as a stored procedure and call
it to run it. You can also pass parameters to stored procedures so
that the stored procedure can act on the passed parameter values.
Stored Procedures are created to perform one or more DML operations
on Database. It is nothing but the group of SQL statements that
accepts some input in the form of parameters and performs some task
and may or may not return a value. 
Syntax
CREATE PROCEDURE procedure_name
(parameter1 data_type, parameter2 data_type, …)
AS
BEGIN
 — SQL statements to be executed
END
To Execute the procedure
EXEC procedure_name parameter1_value, parameter2_value, ..

Parameter Explanation

The most important part is the parameters. Parameters are used to


pass values to the Procedure. There are different types of
parameters, which are as follows: 
1. BEGIN: This is what directly executes or we can say that it
is an executable part.
2. END: Up to this, the code will get executed.

Example:

Imagine a database named “SampleDB”, a table named “Customers” with


some sample data, and a stored procedure named”
GetCustomersByCountry”. The stored procedure takes the parameter
“Country” and returns a list of customers from the “Customers”
table that matches the specified country. Finally, the stored
procedure is executed with the parameter “Sri Lanka” to retrieve
the list of customers from Sri Lanka.
-- Create a new table named "Customers"

CREATE TABLE Customers (


CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
ContactName VARCHAR(50),
Country VARCHAR(50)
);
-- Insert some sample data into the Customers table

INSERT INTO Customers (CustomerID, CustomerName, ContactName,


Country)
VALUES (1, 'Shubham', 'Thakur', 'India'),
(2, 'Aman ', 'Chopra', 'Australia'),
(3, 'Naveen', 'Tulasi', 'Sri lanka'),
(4, 'Aditya', 'Arpan', 'Austria'),
(5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain');

-- Create a stored procedure named "GetCustomersByCountry"

CREATE PROCEDURE GetCustomersByCountry


@Country VARCHAR(50)
AS
BEGIN
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = @Country;
END;

-- Execute the stored procedure with parameter "Sri lanka"

EXEC GetCustomersByCountry @Country = 'Sri lanka';

You might also like