upgrade a procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

upgrade a procedure

Author

Topic

marclas
Starting Member

16 Posts

Posted - 2012-05-10 : 03:46:41

Hi
i wanted to know if is good to write a procedure like this one
[code="sql"]
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
[/code]

Marclas

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-10 : 07:50:55

quote:
i wanted to know if is good to write a procedure like this one

I don't see anything fundamentally wrong with the stored procedure. If I understood the logic correctly, it gets a few parameters and inserts or updates three tables. For that, what you have is just fine. There may be opportunities to make it more compact or more elegant etc., but if it is working well, I don't see any need to make changes.

I would add a few comments and format the code nicely to make it easy to read and maintain.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources