1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
|
CREATE PROCEDURE usr_CreateNewILT
-- Add the parameters for the stored procedure here
@ProductID int,
@SerialID nchar(50),
@UserID int
AS
DECLARE @date datetime
DECLARE @ILTID int
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET IDENTITY_INSERT dbo.ILT OFF
-- Insert statements for procedure here
BEGIN TRANSACTION
-- Is the product ID exist?
IF NOT EXISTS (SELECT * FROM [APRProduction].[dbo].[Product] WHERE [ID]=@ProductID)
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
SELECT @date = GETDATE()
--SELECT @UserID = [ID] FROM [APRProduction].[dbo].[Utilisateur] WHERE [Username]=@Username
INSERT INTO dbo.ILT WITH (TABLOCKX) (ProductID, SerialID, Date, Score, nbimpacts, Utilisateur_ID )
VALUES (@ProductID, @SerialID, @date, 0, 0, @UserID)
IF (@@ERROR <> 0) OR (@@ROWCOUNT = 0)
GOTO ROLLBACK_ON_ERROR
SELECT @ILTID = SCOPE_IDENTITY()
COMMIT TRANSACTION
RETURN @ILTID
ROLLBACK_ON_ERROR:
ROLLBACK TRANSACTION
RETURN -1
END
GO |
Partager