GridView-DetailsView (Master/Detail) Control

USE [Northwind]

--******************************************************************************
-- Description: Stored Procedures for the table: Orders
-- GENERATED FROM MSCDCodeGenerator
--******************************************************************************

--=================================================================
-- INSERT procedures
--=================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[Orders_Insert]
GO

CREATE PROCEDURE [dbo].[Orders_Insert] (
	@CustomerID  nchar(10), 
	@EmployeeID  int, 
	@OrderDate  datetime, 
	@RequiredDate  datetime, 
	@ShippedDate  datetime, 
	@ShipVia  int, 
	@Freight  money, 
	@ShipName  nvarchar(80), 
	@ShipAddress  nvarchar(120), 
	@ShipCity  nvarchar(30), 
	@ShipRegion  nvarchar(30), 
	@ShipPostalCode  nvarchar(20), 
	@ShipCountry  nvarchar(30), 
	@OrderID  int OUTPUT,
	@ErrorCode int OUTPUT
	) 
AS  
	INSERT INTO [Orders] (
		[CustomerID], 
		[EmployeeID], 
		[OrderDate], 
		[RequiredDate], 
		[ShippedDate], 
		[ShipVia], 
		[Freight], 
		[ShipName], 
		[ShipAddress], 
		[ShipCity], 
		[ShipRegion], 
		[ShipPostalCode], 
		[ShipCountry])  
 	VALUES (
 		@CustomerID, 
 		@EmployeeID, 
 		@OrderDate, 
 		@RequiredDate, 
 		@ShippedDate, 
 		@ShipVia, 
 		@Freight, 
 		@ShipName, 
 		@ShipAddress, 
 		@ShipCity, 
 		@ShipRegion, 
 		@ShipPostalCode, 
 		@ShipCountry)  
  
 	-- Get the IDENTITY value for the row just inserted.
	SET @OrderID = SCOPE_IDENTITY()  

	-- Get the Error Code for the statement just executed.
	SELECT @ErrorCode=@@ERROR
GO

--=================================================================
-- UPDATE procedures
--=================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[Orders_Update]
GO

CREATE PROCEDURE [dbo].[Orders_Update](
	@OrderID int, 
	@CustomerID nchar(10), 
	@EmployeeID int, 
	@OrderDate datetime, 
	@RequiredDate datetime, 
	@ShippedDate datetime, 
	@ShipVia int, 
	@Freight money, 
	@ShipName nvarchar(80), 
	@ShipAddress nvarchar(120), 
	@ShipCity nvarchar(30), 
	@ShipRegion nvarchar(30), 
	@ShipPostalCode nvarchar(20), 
	@ShipCountry nvarchar(30), 
	@ErrorCode int OUTPUT)
AS    
	UPDATE [Orders] SET 
		[CustomerID]=@CustomerID, 
		[EmployeeID]=@EmployeeID, 
		[OrderDate]=@OrderDate, 
		[RequiredDate]=@RequiredDate, 
		[ShippedDate]=@ShippedDate, 
		[ShipVia]=@ShipVia, 
		[Freight]=@Freight, 
		[ShipName]=@ShipName, 
		[ShipAddress]=@ShipAddress, 
		[ShipCity]=@ShipCity, 
		[ShipRegion]=@ShipRegion, 
		[ShipPostalCode]=@ShipPostalCode, 
		[ShipCountry]=@ShipCountry
	WHERE OrderID=@OrderID

	-- Get the Error Code for the statement just executed.
	SELECT @ErrorCode=@@ERROR
GO

--=================================================================
-- DELETE procedures
--=================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[Orders_Delete]
GO

CREATE PROCEDURE [dbo].[Orders_Delete](
	@OrderID int, 
	@ErrorCode int OUTPUT) 
AS  
	DELETE FROM [Orders]   
	WHERE [OrderID] = @OrderID

	-- Get the Error Code for the statement just executed.
	SELECT @ErrorCode=@@ERROR
GO


--=================================================================
-- SELECT ALL procedures
--=================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[Orders_SelectAll]
GO

CREATE PROCEDURE [dbo].[Orders_SelectAll] 
	@ErrorCode int OUTPUT
AS  
	SELECT 
		[OrderID],
		[CustomerID],
		[EmployeeID],
		[OrderDate],
		[RequiredDate],
		[ShippedDate],
		[ShipVia],
		[Freight],
		[ShipName],
		[ShipAddress],
		[ShipCity],
		[ShipRegion],
		[ShipPostalCode],
		[ShipCountry]
	FROM [Orders]   

	-- Get the Error Code for the statement just executed.
	SELECT @ErrorCode=@@ERROR
GO


--=================================================================
-- SELECT ONE procedures
--=================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[Orders_SelectOne]
GO

CREATE PROCEDURE [dbo].[Orders_SelectOne](
	@OrderID  int,
	@ErrorCode int OUTPUT)
AS
	SELECT 
		[OrderID], 
		[CustomerID], 
		[EmployeeID], 
		[OrderDate], 
		[RequiredDate], 
		[ShippedDate], 
		[ShipVia], 
		[Freight], 
		[ShipName], 
		[ShipAddress], 
		[ShipCity], 
		[ShipRegion], 
		[ShipPostalCode], 
		[ShipCountry]
	FROM [Orders] 
	WHERE [OrderID]=@OrderID

	-- Get the Error Code for the statement just executed.
	SELECT @ErrorCode=@@ERROR
GO

--=================================================================
-- SELECT BY FOREIGN KEY procedures
--=================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders_SelectByForeignKey]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[Orders_SelectByForeignKey]
GO
CREATE PROCEDURE [dbo].[Orders_SelectByForeignKey] 
	@Column varchar(255), 
	@Value varchar(255),
	@ErrorCode int OUTPUT
AS  
	DECLARE @RC varchar(4000)

	SET @RC = 'SELECT [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]
	FROM [Orders] WHERE CONVERT(varchar(255), [' + @Column + ']) = CONVERT(varchar(255), ' + CHAR(39) + @Value + CHAR(39) + ')'
	EXEC(@RC)
	-- Get the Error Code for the statement just executed.
	SELECT @ErrorCode=@@ERROR

GO

--=================================================================
-- SELECT BY TOP NUMBER procedures
--=================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders_SelectByTopNumber]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[Orders_SelectByTopNumber]
GO
CREATE PROCEDURE [dbo].[Orders_SelectByTopNumber] 
	@NumRows int, 
	@Last bit,
	@ErrorCode int OUTPUT
AS  
	DECLARE @RC varchar(4000)

	IF (@Last = 1)
		SET @RC = 'SELECT TOP ' + CAST(@NumRows AS varchar) + ' t1.[OrderID], t1.[CustomerID], t1.[EmployeeID], t1.[OrderDate], t1.[RequiredDate], t1.[ShippedDate], t1.[ShipVia], t1.[Freight], t1.[ShipName], t1.[ShipAddress], t1.[ShipCity], t1.[ShipRegion], t1.[ShipPostalCode], t1.[ShipCountry]
		FROM [Orders] t1'
	ELSE
		SET @RC = 'SELECT t2.[OrderID], t2.[CustomerID], t2.[EmployeeID], t2.[OrderDate], t2.[RequiredDate], t2.[ShippedDate], t2.[ShipVia], t2.[Freight], t2.[ShipName], t2.[ShipAddress], t2.[ShipCity], t2.[ShipRegion], t2.[ShipPostalCode], t2.[ShipCountry]
		FROM (SELECT TOP ' + CAST(@NumRows AS varchar) + ' t1.[OrderID], t1.[CustomerID], t1.[EmployeeID], t1.[OrderDate], t1.[RequiredDate], t1.[ShippedDate], t1.[ShipVia], t1.[Freight], t1.[ShipName], t1.[ShipAddress], t1.[ShipCity], t1.[ShipRegion], t1.[ShipPostalCode], t1.[ShipCountry]
		FROM [Orders] t1 ORDER BY 1 DESC) t2 ORDER BY 1 ASC'
	
	EXEC(@RC)
	-- Get the Error Code for the statement just executed.
	SELECT @ErrorCode=@@ERROR
	
GO

--=================================================================
-- SELECT BY ROW RANGE procedures
--=================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders_SelectByRowRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[Orders_SelectByRowRange]
GO
CREATE PROCEDURE [dbo].[Orders_SelectByRowRange] 
	@FirstRow int, 
	@LastRow int,
	@ErrorCode int OUTPUT
AS  
	DECLARE @RC varchar(4000)

	SET @RC = 'SELECT  [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]
		FROM (SELECT  ROW_NUMBER() OVER (ORDER BY OrderID)
      	AS Row, [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry] FROM [Orders]) AS OrdersWithRowNumbers
		WHERE  Row >= ' + CAST(@FirstRow AS varchar) + ' AND Row <= ' + CAST(@LastRow AS varchar) + ''

	EXEC(@RC)
	-- Get the Error Code for the statement just executed.
	SELECT @ErrorCode=@@ERROR	
	
-- [End of Stored Procedures for table: Orders]
-- ========================================================================================================
GO