1CREATE PROCEDURE spGetEmployeeCountByGender
2@Gender nvarchar(20),
3@EmployeeCount int Output
4AS
5BEGIN
6SELECT @EmployeeCount = COUNT(Id)
7FROM tblEmployee
8WHERE Gender = @Gender
9END
1IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
2 DROP PROCEDURE Production.uspGetList;
3GO
4CREATE PROCEDURE Production.uspGetList @Product varchar(40)
5 , @MaxPrice money
6 , @ComparePrice money OUTPUT
7 , @ListPrice money OUT
8AS
9 SET NOCOUNT ON;
10 SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
11 FROM Production.Product AS p
12 JOIN Production.ProductSubcategory AS s
13 ON p.ProductSubcategoryID = s.ProductSubcategoryID
14 WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
15-- Populate the output variable @ListPprice.
16SET @ListPrice = (SELECT MAX(p.ListPrice)
17 FROM Production.Product AS p
18 JOIN Production.ProductSubcategory AS s
19 ON p.ProductSubcategoryID = s.ProductSubcategoryID
20 WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
21-- Populate the output variable @compareprice.
22SET @ComparePrice = @MaxPrice;
23GO
24