Jimmy

USE [Northwind]

--1 Create a stored Procedure show all Categories
go
CREATE PROCEDURE sp_Categories_Display

AS
BEGIN

Select *
From Categories

END
Go

--2 Create a stored Procedure to search a categories by id ---------------------------

Go
CREATE PROCEDURE sp_Categories_SearchID
@CategoryID int

AS
BEGIN

Select CategoryID
From Categories
Where CategoryID = @CategoryID

END
GO

--3 Create a stored Procedure to update a categories--------------------------

CREATE PROCEDURE sp_Categories_Update

@CategoryID int,
@CategoryName nvarchar(20)

AS
BEGIN

Update Categories
Set CategoryName = @CategoryName
Where CategoryID = @CategoryID


END
GO

--4Create a stored Procedure to insert  a category---------------------------

CREATE PROCEDURE sp_Categories_InsertCategory

@CategoryID int,
@CategoryName nvarchar(20)

AS
BEGIN

Insert into Categories(CategoryID, CategoryName)
Values(@CategoryID, @CategoryName)

END
GO

--5 Create a stored Procedure to delete  a category----------------------

CREATE PROCEDURE sp_Categories_DeleteCategory

@CategoryID int

AS
BEGIN

Delete from Categories
Where CategoryID = @CategoryID

END
GO

--6 Create a stored Procedure to display all categories and their products

CREATE PROCEDURE sp_Categories_DisplayCategoryAndProducts

AS
BEGIN

Select c.CategoryName, products.Products
From Category
Inner join Products on CategoryID

END
GO

--7 Create a stored Procedure display according given word 'sample'
  --if it is sample display top 5 categories
  --Else display all
--8 Create a stored Procedure to find employees by their lastname
  --if not lastname is specified, return all employees
--9 Create a stored procedure to show all products customers ordered group by productname
  --and total of each group of products

---10 --CREATE A SPROC TO FIND THE GENDER OF THE EMPLOYEES