This lab introduce students to stored procedures, their use and some examples such that user should be able to write them for their semester projects where necessary
This lab introduces the stored procedures and discuss several examples of how effectively can we use stored procedures. Also make students learn how to pass parameters to stored procedures.
A procedure that you write in SQL to replace writing some reusable queries again and again such that results can be filtered with parameters. Instead of calling multiple statements from your application you can call the stored procedure to do a batch of work with just one statement.
In SQL Server Management Studio you can define a new one by right-click on "Stored Procedures" under "Programmability" and click on Stored Procedure to open default template:
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> = ,
<@Param2, sysname, @p2> =
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
Create a stored procedure to get the last added employee.
CREATE PROCEDURE getLastEmployee
AS
BEGIN
SELECT TOP 1 * FROM [northwindd].[dbo].[Employees] order by EmployeeID desc
END
GO
To run it use:
EXEC getLastEmployee
Create a stored procedure to get employee by FirstName.
CREATE PROCEDURE getEmployeeByFirstName
@LastName NVARCHAR(50)
AS
BEGIN
SELECT * FROM [northwindd].[dbo].[Employees] where FirstName=@LastName;
END
GO
To run it use:
EXEC getEmployeeByFirstName 'Ali'
or
EXEC getEmployeeByFirstName @LastName='Ali'
Create a stored procedure to get employee id who process the last order in a given year.
CREATE PROCEDURE getEmployeeIDWithMostOrdersByYear
@YearDate NVARCHAR(4),
@EmployeeId INT output
AS
BEGIN
SELECT TOP 1 @EmployeeId = [EmployeeID]
FROM [northwindd].[dbo].[Orders]
GROUP By EmployeeID, OrderDate
having YEAR(OrderDate) = @YearDate
order by OrderDate desc
END
GO
To run it use:
DECLARE @EmployeeID INT
SET @EmployeeID = 0
EXEC getEmployeeIDWithMostOrdersByYear @YearDate ='1997', @EmployeeID =@EmployeeId OUTPUT
SELECT @EmployeeID as EmployeeID
Northwind database has already a number of stored procedures. Try to understand them, execute them and write similar stored procedures for your semester project.
ALTER PROCEDURE [dbo].[Customers By City]
-- Add the parameters for the stored procedure here
(@param1 NVARCHAR(20), @order bit)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if(@order = 0)
BEGIN
SELECT CustomerID, ContactName, CompanyName, City from Customers as c where c.City=@param1
END
else
BEGIN
SELECT CustomerID, ContactName, CompanyName, City from Customers as c where c.City=@param1 order by CustomerID desc
END
END
ALTER PROCEDURE [dbo].[Customers Count By Region]
-- Add the parameters for the stored procedure here
(@param1 NVARCHAR(15))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @count int
SELECT @count = COUNT(*)FROM Customers WHERE Customers.Region = @Param1
RETURN @count
END
ALTER PROCEDURE [dbo].[CustOrderHist] @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
ALTER PROCEDURE [dbo].[CustOrdersDetail] @OrderID int
AS
SELECT ProductName,
UnitPrice=ROUND(Od.UnitPrice, 2),
Quantity,
Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID
To earn PASS status, system is about to evaluate you using multiple choice questions (MCQs) and True/False type questions.
Topics |
|
