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;