May 10, 2012 at 1:45 am
Hi
i wanted to know if is good to write a procedure like this one
CREATE PROCEDURE dbo.MAJStatutDossier
-- Add the parameters for the stored procedure here
@StaId int,
@DosID uniqueidentifier,
@staBL nvarchar (50)= null,
@staCRprod nvarchar (50)=null,
@SfiId uniqueidentifier = '00000000-0000-0000-0000-000000000000' OUTPUT,
@SfiRefDoc nvarchar (50),
@filID uniqueidentifier = '00000000-0000-0000-0000-000000000000' OUTPUT,
@fillNameSend nvarchar (50),
@filDateSend datetime,
@filNameReturn nvarchar(50) = null,
@filDateReturn datetime = null,
@filSceDest nvarchar (50)= null,
@filDateRelance datetime = null,
@filRefRelance nvarchar (50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
------------
IF @SfiId IS NULL OR @SfiId = '00000000-0000-0000-0000-000000000000'
BEGIN
SET @SfiId = NEWID()
END
---------------
IF @filID IS NULL OR @filID = '00000000-0000-0000-0000-000000000000'
BEGIN
SET @filID = NEWID()
END
-- Insert statements for procedure here
-- Insert statements for procedure here
IF NOT EXISTS (SELECT dbo.CSR_STATUT.DOS_ID FROM CSR_STATUT
WHERE DOS_ID = @DosID )
BEGIN
INSERT INTO CSR_STATUT
VALUES (@StaId,CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME),@staBL,@staCRprod,@DosID )
--------
INSERT INTO dbo.CSR_SENDFILE
VALUES (@SfiId ,@SfiRefDoc ,@DosId )
--------
INSERT INTO dbo.CSR_FILE
VALUES (@filID ,@fillNameSend ,@filDateSend ,@filNameReturn ,@filDateReturn ,@filSceDest ,@filDateRelance ,@filRefRelance )
END
ELSE
BEGIN
UPDATE CSR_STATUT
SET STA_ID = @StaId , STA_DATE = CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS FLOAT)) AS DATETIME),
STA_BL =@staBL ,STA_CRPROD =@staCRprod
WHERE DOS_ID = @DosID
--------------------------enreg de l envoi du fichier
IF NOT EXISTS (SELECT * FROM dbo.CSR_SENDFILE WHERE DOS_ID = @DosId )
BEGIN
INSERT INTO dbo.CSR_SENDFILE
VALUES (@SfiId ,@SfiRefDoc ,@DosId )
END
ELSE
BEGIN
UPDATE dbo.CSR_SENDFILE
SET SFI_ID = @SfiId , SFI_REFDOC = @SfiRefDoc
WHERE DOS_ID = @DosId
END
-----------------------------ereng
IF NOT EXISTS (SELECT * FROM dbo.CSR_FILE WHERE FIL_ID = @filID )
BEGIN
INSERT INTO dbo.CSR_FILE
VALUES (@filID ,@fillNameSend ,@filDateSend ,@filNameReturn ,@filDateReturn ,@filSceDest ,@filDateRelance ,@filRefRelance )
END
ELSE
BEGIN
UPDATE dbo.CSR_FILE
SET FIL_NAMESEND =@fillNameSend ,FIL_DATESEND = @filDateSend , FIL_NAMERETURN = @filNameReturn ,
FIL_DATERETURN = @filDateReturn ,FIL_SVCDESTINATAIRE = @filSceDest ,FIL_DATERELANCE = @filDateRelance ,
FIL_REFRELANCE = @filRefRelance
WHERE FIL_ID =@filID
END
END
END
GO
May 10, 2012 at 2:52 am
Looks fine to me.
I would error handling and transaction control (if it's not managed by the caller application)...
May 10, 2012 at 3:05 am
hi,
so i should write begin transaction instead of begin ...and end transaction at the end???
May 10, 2012 at 4:27 am
BEGIN and END just determine the logical block of statements.
It's nothing to do with transactions.
Transactions are controlled by:
BEGIN TRANSACTION
COMMIT TRANSACTION
and
ROLLBACK TRANSACTION
statements (or shorter variations of them - all in the BoL)
To control transaction from within proc you also would want to use BEGIN TRY ... CATCH error handling, so you can roll back transaction on error or commit it where there are none.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply