SQL Server Stored Procedure

Go Back

Level : Advanced

Go Back

Mentor: Shailendra Chauhan

Duration : 00:02:00

Get Details

Creating a Simple Stored Procedure

A simple stored procedure in SQL Server is a reusable block of SQL code that performs a specific task.

Example

CREATE PROCEDURE SimpleProcedure
AS
BEGIN
  SELECT * FROM Customers;
END;

Creating a Stored Procedure with Parameters

You can create a stored procedure with parameters to make it more versatile and customizable.

Example

CREATE PROCEDURE GetCustomerByID
@CustomerID INT
AS
BEGIN
  SELECT * FROM Customers WHERE CustomerID = @CustomerID;
END;

Creating a Stored Procedure with Default Parameter Values

You can set default values for parameters in a stored procedure to provide flexibility.

Example

CREATE PROCEDURE GetOrdersByDate
@StartDate DATE = '2023-01-01',
@EndDate DATE = '2023-12-31'
AS
BEGIN
  SELECT * FROM Orders WHERE OrderDate BETWEEN @StartDate AND @EndDate;
END;

Creating a Stored Procedure with an Output Parameter

An output parameter allows a stored procedure to return a value to the caller.

Example

CREATE PROCEDURE GetEmployeeSalary
@EmployeeID INT,
@Salary DECIMAL(10, 2) OUTPUT
AS
BEGIN
  SELECT @Salary = Salary FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Creating an Encrypted Stored Procedure

You can encrypt the source code of a stored procedure to protect its contents.

Example

CREATE PROCEDURE EncryptedProcedure
WITH ENCRYPTION
AS
BEGIN
  -- Your encrypted SQL code here
END;

Creating a Temporary Procedure

Temporary procedures are only available during the session and are useful for temporary tasks.

Example

CREATE PROCEDURE #TempProcedure
AS
BEGIN
  -- Your temporary procedure code here
END;

Modifying the Stored Procedure

You can modify an existing stored procedure to update its functionality.

Example

ALTER PROCEDURE GetCustomerByID
@CustomerID INT,
@CountryCode NVARCHAR(3)
AS
BEGIN
  SELECT * FROM Customers WHERE CustomerID = @CustomerID AND CountryCode = @CountryCode;
END;

Renaming the Stored Procedure

You can rename an existing stored procedure to give it a new name.

Example

EXEC sp_rename 'OldProcedureName', 'NewProcedureName';

Stored Procedure Inserting Data

A stored procedure can be used to insert data into a database table.

Example

CREATE PROCEDURE InsertCustomer
@Name NVARCHAR(50),
@Email NVARCHAR(100)
AS
BEGIN
  INSERT INTO Customers (Name, Email) VALUES (@Name, @Email);
END;

Stored Procedure Deleting Data

A stored procedure can be used to delete data from a database table.

Example

CREATE PROCEDURE DeleteCustomer
@CustomerID INT
AS
BEGIN
  DELETE FROM Customers WHERE CustomerID = @CustomerID;
END;

Stored Procedure Updating Data

A stored procedure can be used to update data in a database table.

Example

CREATE PROCEDURE UpdateProductPrice
@ProductID INT,
@NewPrice DECIMAL(10, 2)
AS
BEGIN
  UPDATE Products SET Price = @NewPrice WHERE ProductID = @ProductID;
END;

Stored Procedure Encrypt, Alter Rename Drop

A sequence of actions to encrypt, alter, rename, and drop a stored procedure.

Example

ALTER PROCEDURE OldProcedureName
WITH ENCRYPTION
AS
BEGIN
  -- Your encrypted SQL code here
END;
EXEC sp_rename 'OldProcedureName', 'NewProcedureName';
DROP PROCEDURE NewProcedureName;

Self-paced Membership

Upto 60% OFF

Know More

Still have some questions? Let's discuss.