Database Stored Procedure instance in asp.net

The advantages of stored procedures when using ms SQL Server to create an application, Transaction-SQL is a major programming language. If you use Transaction-SQL for programming, there are two methods. First, store the Transaction-SQL program locally and create an application to send commands to SQL Server to process the results.

Second, some programs written using Transaction-SQL can be stored in SQL Server as stored procedures, and applications can be created to call stored procedures, the stored procedure of processing data results can return the result set to the caller by receiving parameters. The format of the result set is determined by the caller. The status value is returned to the caller, indicating whether the call is successful or failed; includes database operation statements, and can call another stored procedure in one stored procedure.

Example

The Code is as follows:

Copy code

-- Table structure USE [OnlyTest] GO   /***** Object: Table [dbo]. [Stock] Script Date: 11/30/2012 18:10:05 ******/ If exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [Stock] ') AND type in (n'u ')) Drop table [dbo]. [Stock] GO   USE [OnlyTest] GO   /***** Object: Table [dbo]. [Stock] Script Date: 11/30/2012 18:10:05 ******/ SET ANSI_NULLS ON GO   SET QUOTED_IDENTIFIER ON GO   Create table [dbo]. [Stock] ( [StockID] [bigint] IDENTITY (1, 1) not null, [ProductID] [int] not null, [StockDate] [date] not null, [StockQty] [int] not null, CONSTRAINT [PK_Stock] PRIMARY KEY CLUSTERED ( [ProductID] ASC, [StockDate] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]   GO         USE [OnlyTest] GO   /***** Object: StoredProcedure [dbo]. [usp_OptStock] Script Date: 11/30/2012 18:09:35 ******/ If exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [usp_OptStock] ') AND type in (n'p', n'pc ')) Drop procedure [dbo]. [usp_OptStock] GO   /***** Object: StoredProcedure [dbo]. [usp_OptStock_SmallCode] Script Date: 11/30/2012 18:09:35 ******/ If exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [usp_OptStock_SmallCode] ') AND type in (n'p', n'pc ')) Drop procedure [dbo]. [usp_OptStock_SmallCode] GO   USE [OnlyTest] GO   /***** Object: StoredProcedure [dbo]. [usp_OptStock] Script Date: 11/30/2012 18:09:35 ******/ SET ANSI_NULLS ON GO   SET QUOTED_IDENTIFIER ON GO     -- Concurrent inventory, standard practice Create procedure [dbo]. [usp_OptStock] @ ProductID INT, @ StartDate DATE, @ EndDate DATE, @ OptNumber INT AS DECLARE @ OptDays INT; DECLARE @ HaveDays INT; SET @ OptDays = DATEDIFF (day, @ StartDate, @ EndDate) + 1;   Begin transaction; SELECT @ HaveDays = COUNT (*) FROM dbo. Stock WHERE StockQty >=@ OptNumber AND StockDate> = @ StartDate AND StockDate <= @ EndDate AND ProductID = @ ProductID IF @ HaveDays = @ OptDays BEGIN UPDATE dbo. Stock SET StockQty = StockQty-@ OptNumber WHERE StockQty >=@ OptNumber AND StockDate> = @ StartDate AND StockDate <= @ EndDate AND ProductID = @ ProductID COMMIT; RETURN 0; END ELSE BEGIN ROLLBACK; RETURN-110; END   GO   /***** Object: StoredProcedure [dbo]. [usp_OptStock_SmallCode] Script Date: 11/30/2012 18:09:35 ******/ SET ANSI_NULLS ON GO   SET QUOTED_IDENTIFIER ON GO     -- Concurrent inventory, reckless practices. Create procedure [dbo]. [usp_OptStock_SmallCode] @ ProductID INT, @ StartDate DATE, @ EndDate DATE, @ OptNumber INT AS DECLARE @ OptDays INT; SET @ OptDays = DATEDIFF (day, @ StartDate, @ EndDate) + 1;   BEGIN TRANSACTION   UPDATE dbo. Stock SET StockQty = StockQty-@ OptNumber WHERE StockQty >=@ OptNumber AND StockDate> = @ StartDate AND StockDate <= @ EndDate AND ProductID = @ ProductID IF @ ROWCOUNT = @ OptDays BEGIN COMMIT RETURN 0 END ELSE BEGIN ROLLBACK RETURN-110 END   GO               -- Test code USE OnlyTest   GO   Truncate table dbo. Stock   Insert into dbo. Stock VALUES (1, '2014-1-1 ', 1 ), (1, '2014-1-2 ', 2 ), (1, '2014-1-3 ', 3 ), (1, '2014-1-4 ', 4 ), (1, '2014-1-5 ', 5)     DECLARE @ return_value int   EXEC @ return_value = [dbo]. [usp_OptStock] @ ProductID = 1, @ StartDate = '2014-1-2 ', @ EndDate = '2014-1-4 ', @ OptNumber = 2   SELECT 'Return sufficient inventory '= @ return_value SELECT * FROM dbo. Stock   EXEC @ return_value = [dbo]. [usp_OptStock] @ ProductID = 1, @ StartDate = '2014-1-2 ', @ EndDate = '2014-1-4 ', @ OptNumber = 2   SELECT 'Return from stock shortage '= @ return_value SELECT * FROM dbo. Stock