----------------------------------------------------------------
-- [dbo].[Categories] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Categories_GetAll')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[Categories_GetAll] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Categories_GetAll]
AS
BEGIN
SET NOCOUNT ON
SELECT
[categories].[CategoryID] AS 'CategoryID',
[categories].[CategoryName] AS 'CategoryName',
[categories].[Description] AS 'Description',
[categories].[Picture] AS 'Picture'
FROM [dbo].[Categories] [categories]
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Categories_GetById')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[Categories_GetById] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Categories_GetById]
@categoryID int
AS
BEGIN
SET NOCOUNT ON
SELECT
[categories].[CategoryID] AS 'CategoryID',
[categories].[CategoryName] AS 'CategoryName',
[categories].[Description] AS 'Description',
[categories].[Picture] AS 'Picture'
FROM [dbo].[Categories] [categories]
WHERE [CategoryID]=@categoryID
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertCategories')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[InsertCategories] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[InsertCategories]
@categoryID int OUT,
@categoryName nvarchar(15),
@description ntext = NULL,
@picture image = NULL
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
INSERT INTO [dbo].[Categories] ([CategoryName], [Description], [Picture])
VALUES (@categoryName, @description, @picture)
SET @categoryID = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
EXEC RethrowError;
END CATCH
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateCategories')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[UpdateCategories] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[UpdateCategories]
@categoryID int,
@categoryName nvarchar(15),
@description ntext = NULL,
@picture image = NULL
AS
BEGIN
--The [dbo].[Categories] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
SET NOCOUNT ON
BEGIN TRY
UPDATE [dbo].[Categories]
SET [CategoryName] = @categoryName, [Description] = @description, [Picture] = @picture
WHERE [CategoryID]=@categoryID
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
END
END TRY
BEGIN CATCH
EXEC RethrowError;
END CATCH
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Categories_Delete')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[Categories_Delete] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Categories_Delete]
@categoryID int
AS
BEGIN
SET NOCOUNT ON
DELETE FROM [dbo].[Categories]
WHERE [CategoryID]=@categoryID
SET NOCOUNT OFF
END
GO
--The GetByFK stored procedure cannot be created because the [dbo].[Categories] table doesn't have at least one foreign key column or the foreign keys are also primary keys.
----------------------------------------------------------------
-- [dbo].[Products] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Products_GetAll')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[Products_GetAll] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Products_GetAll]
AS
BEGIN
SET NOCOUNT ON
SELECT
[products].[CategoryID] AS 'CategoryID',
[products].[Discontinued] AS 'Discontinued',
[products].[ProductID] AS 'ProductID',
[products].[ProductName] AS 'ProductName',
[products].[QuantityPerUnit] AS 'QuantityPerUnit',
[products].[ReorderLevel] AS 'ReorderLevel',
[products].[SupplierID] AS 'SupplierID',
[products].[UnitPrice] AS 'UnitPrice',
[products].[UnitsInStock] AS 'UnitsInStock',
[products].[UnitsOnOrder] AS 'UnitsOnOrder'
FROM [dbo].[Products] [products]
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Products_GetById')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[Products_GetById] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Products_GetById]
@productID int
AS
BEGIN
SET NOCOUNT ON
SELECT
[products].[CategoryID] AS 'CategoryID',
[products].[Discontinued] AS 'Discontinued',
[products].[ProductID] AS 'ProductID',
[products].[ProductName] AS 'ProductName',
[products].[QuantityPerUnit] AS 'QuantityPerUnit',
[products].[ReorderLevel] AS 'ReorderLevel',
[products].[SupplierID] AS 'SupplierID',
[products].[UnitPrice] AS 'UnitPrice',
[products].[UnitsInStock] AS 'UnitsInStock',
[products].[UnitsOnOrder] AS 'UnitsOnOrder'
FROM [dbo].[Products] [products]
WHERE [ProductID]=@productID
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertProducts')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[InsertProducts] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[InsertProducts]
@categoryID int,
@discontinued bit,
@productID int OUT,
@productName nvarchar(40),
@quantityPerUnit nvarchar(20) = NULL,
@reorderLevel smallint = NULL,
@supplierID int = NULL,
@unitPrice money = NULL,
@unitsInStock smallint = NULL,
@unitsOnOrder smallint = NULL
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
INSERT INTO [dbo].[Products] ([CategoryID], [Discontinued], [ProductName], [QuantityPerUnit], [ReorderLevel], [SupplierID], [UnitPrice], [UnitsInStock], [UnitsOnOrder])
VALUES (@categoryID, @discontinued, @productName, @quantityPerUnit, @reorderLevel, @supplierID, @unitPrice, @unitsInStock, @unitsOnOrder)
SET @productID = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
EXEC RethrowError;
END CATCH
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateProducts')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[UpdateProducts] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[UpdateProducts]
@categoryID int,
@discontinued bit,
@productID int,
@productName nvarchar(40),
@quantityPerUnit nvarchar(20) = NULL,
@reorderLevel smallint = NULL,
@supplierID int = NULL,
@unitPrice money = NULL,
@unitsInStock smallint = NULL,
@unitsOnOrder smallint = NULL
AS
BEGIN
--The [dbo].[Products] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
SET NOCOUNT ON
BEGIN TRY
UPDATE [dbo].[Products]
SET [CategoryID] = @categoryID, [Discontinued] = @discontinued, [ProductName] = @productName, [QuantityPerUnit] = @quantityPerUnit, [ReorderLevel] = @reorderLevel, [SupplierID] = @supplierID, [UnitPrice] = @unitPrice, [UnitsInStock] = @unitsInStock, [UnitsOnOrder] = @unitsOnOrder
WHERE [ProductID]=@productID
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
END
END TRY
BEGIN CATCH
EXEC RethrowError;
END CATCH
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Products_Delete')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[Products_Delete] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Products_Delete]
@productID int
AS
BEGIN
SET NOCOUNT ON
DELETE FROM [dbo].[Products]
WHERE [ProductID]=@productID
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Products_GetByCategories')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[Products_GetByCategories] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Products_GetByCategories]
@categoryID int
AS
BEGIN
SET NOCOUNT ON
SELECT
[products].[CategoryID] AS 'CategoryID',
[products].[Discontinued] AS 'Discontinued',
[products].[ProductID] AS 'ProductID',
[products].[ProductName] AS 'ProductName',
[products].[QuantityPerUnit] AS 'QuantityPerUnit',
[products].[ReorderLevel] AS 'ReorderLevel',
[products].[SupplierID] AS 'SupplierID',
[products].[UnitPrice] AS 'UnitPrice',
[products].[UnitsInStock] AS 'UnitsInStock',
[products].[UnitsOnOrder] AS 'UnitsOnOrder'
FROM [dbo].[Products] [products]
WHERE [CategoryID]=@categoryID
SET NOCOUNT OFF
END
GO
Submit an article or tip