Create a transaction (sqlsever) in the stored procedure, the unique primary key is automatically generated (format: Custom letter + time + five digits, for example: S2014103010001)

Description:

1. Use SQL stored procedures with transactions.

2, when adding data, automatically create a unique primary key format "one letter" + "time format" + "a five-digit"

3, using the cursor, through another table of multiple data, to the current table data added, and automatically produce a unique primary key format as above.

4. Usage scenario: While increasing primary key unique primary table data, read multiple data from another table and then add to child table, and the primary key of production is unique.

5, the use of the environment: SQL Server.

Use [Micromall]
GO
/****** object:storedprocedure [dbo]. [Saleorderiuproc] Script date:10/30/2014 16:13:28 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo]. [Saleorderiuproc]
@clientId VARCHAR (10),
@returnMsg VARCHAR () OUTPUT
As
BEGIN TRAN

DECLARE @itemId VARCHAR (20)--Define the variables used by the cursor
DECLARE @intId VARCHAR (30)--Initialization variable format: "One letter" + "time format" + "10000"

--Definition of the main table variable

DECLARE @maxId INT--(range: 10001-19999) Example: 10001 Note: The value of the largest primary key column on the day of the primary table needs to be checked
DECLARE @newId VARCHAR (30)--generates a unique primary key for "one letter" + "time format" + "@maxId" for example: S2014103010001

--Definition of a child table variable

DECLARE @maxDetailId int--(range: 10001-19999) Example: 10001 Note: Need to Chazi the value of the largest primary key column of the day
DECLARE @newDetailId VARCHAR (30)--generates a unique primary key for "one letter" + "time format" + "@maxId" for example: D2014103010001


--Initialization data: S2014103010000
SET @intId = ' S ' + CONVERT (CHAR (8), GETDATE (), ()) + right (' 0 '
+ CONVERT (VARCHAR (10), 10000),
5)

--primary key removes the maximum 5 digits of the letter time on the day of the main table
SET @maxId = CONVERT (INT, SUBSTRING (SELECT ISNULL (MAX (Saleorderid),
@intId)
From Saleorder
WHERE 0 = DATEDIFF (DD, OrderDate,
GETDATE ())
), 9, 10) + 1
--Create a unique primary key value for the new data in the main table
SET @newId = ' S ' + CONVERT (CHAR (8), GETDATE (), ()) + right (' 0 '
+ CONVERT (VARCHAR), @maxId),
5)
--The primary key of the child table removes the maximum 5 digits of the letter time
SET @maxDetailId = CONVERT (INT, SUBSTRING (SELECT ISNULL (MAX (Saledetid),
@intId)
from [Saledet]
WHERE 0 = DATEDIFF (DD,
CreateDate,
GETDATE ())
), 9, 10)



--Insert Main Table data
INSERT into Saleorder
(Saleorderid, OrderDate)
VALUES (@newId, GETDATE ())

--Insert sub-table data (read from another table and insert multiple strips, using cursor control)

----------------start with a cursor-----------------

--Define a cursor
DECLARE Cursorofresult CURSOR
For
SELECT T.itemid
From (SELECT titemid as ' itemId '
From Temporder
) T
--Open cursor
OPEN Cursorofresult

--fetch data, cursor moves down one line
FETCH NEXT from Cursorofresult to @itemId

While @ @fetch_status = 0--detects if the data was successfully obtained
BEGIN

SET @maxDetailId = @maxDetailId + 1--The MAXID must be processed using cursors because the transaction is not committed

--Create the correct format for the primary key value of the child table
SET @newDetailId = ' S ' + CONVERT (CHAR (8), GETDATE (), 112)
+ Right (' 0 ' + CONVERT (VARCHAR), @maxDetailId), 5)

--Add Order detail information
INSERT into Saledet
(Saledetid,
Saleorderid,
ItemId,
CreateDate
)
VALUES (@newDetailId,
@newId,
@itemId,
GETDATE ()
);

--cursor continues to move Down
FETCH NEXT from Cursorofresult to @itemId
END

Close cursorofresult--Closing Cursors

Deallocate Cursorofresult

----------------ends with a cursor-----------------

--Delete Shopping cart

DELETE from Temporder
WHERE clientId = @clientId


IF @ @ERROR <> 0
BEGIN
ROLLBACK
SELECT @returnMsg = ' 0 '
END
ELSE
BEGIN
COMMIT
SELECT @returnMsg = ' 1 '

END

RETURN @returnMsg

Create a transaction (sqlsever) in the stored procedure, the unique primary key is automatically generated (format: Custom letter + time + five digits, for example: S2014103010001)