InsertUpdateDelete

 
ALTER PROCEDURE [dbo].[sp_Car_Insert]
    @RunDate BIGINT ,
    @CarNo NVARCHAR(50) ,
    @Volume INT ,
    @Weight SMALLINT ,
    @CarName NVARCHAR(50) ,
    @InUse BIT ,
    @UserID INT = NULL ,
    @CarID TINYINT OUTPUT
AS
    BEGIN
        

        BEGIN TRY
            BEGIN TRANSACTION;

            INSERT  INTO dbo.Car
                    ( CarID ,
                      CarNo ,
                      Volume ,
                      Weight ,
                      CarName ,
                      InUse ,
                      UserID ,
                      StartDate
		            )
            OUTPUT  INSERTED.*
            VALUES  ( ( SELECT  ISNULL(MAX(c.CarID), 0) + 1
                        FROM    Car c
                      ) ,
                      @CarNo ,
                      @Volume ,
                      @Weight ,
                      @CarName ,
                      @InUse ,
                      ISNULL(@UserID, dbo.fn_GetUserID()) ,
                      @RunDate
		            );

            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            IF ( @@TRANCOUNT > 0 )
                ROLLBACK TRANSACTION;
		
            EXEC dbo.usp_CatchError @RaisError = 1, @ExtraData = NULL,
                @ErrorId = NULL;
        END CATCH;
    END;
    //----------------------
  
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Car_Update]
	@RunDate BIGINT,
	@CarNo NVARCHAR(50),
	@Volume INT,
	@Weight SMALLINT,
	@CarName NVARCHAR(50),
	@InUse BIT,
	@CarID TINYINT,
	@UserID INT = NULL
AS
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION;
		IF @UserID IS NULL
		    SET @UserID = dbo.fn_GetUserID();
		
		RAISERROR('امکان ويرايش خودرو وجود ندارد', 18, 1)
		INSERT INTO dbo.Car
		  (
		    CarID,
		    CarNo,
		    Volume,
		    WEIGHT,
		    CarName,
		    InUse,
		    UserID,
		    StartDate
		  )
		SELECT @CarID,
		       @CarNo,
		       @Volume,
		       @Weight,
		       @CarName,
		       @InUse,
		       @UserID,
		       @RunDate
		FROM   (
		           UPDATE
		           dbo.Car
		           SET
		           EndDate = @RunDate
		           OUTPUT
		           INSERTED.*
		           FROM
		           (
		               SELECT StartDate
		               FROM   udft_Car(@RunDate) uc
		               WHERE  uc.CarID = @CarID
		           ) ucc
		           WHERE
		           dbo.Car.CarID = @CarID
		           AND dbo.Car.StartDate = ucc.StartDate
		       ) UpC;
		
		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		IF (@@TRANCOUNT > 0)
		    ROLLBACK TRANSACTION;
		
		EXEC dbo.usp_CatchError @RaisError = 1,
		     @ExtraData = NULL,
		     @ErrorId = NULL;
	END CATCH;
END;
//----------------

 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Car_Delete]
    @CarID TINYINT ,
    @RunDate BIGINT ,
    @UserID INT = NULL
AS
    BEGIN
        IF @UserID IS NULL
            SET @UserID = dbo.fn_GetUserID();

        IF EXISTS ( SELECT  1
                    FROM    dbo.Distribute
                    WHERE   CarID = @CarID )
            RAISERROR(
		        'براي اين خودرو ، مسير پخش تعريف شده است.ابتدا مسير پخش را حذف نمائيد',
		        18,
		        1
		    );
               	
        UPDATE  dbo.Car
        SET     EraserUserID = @UserID ,
                EndDate = @RunDate
        WHERE   Car.EndDate = dbo.fn_GetMaxDateTime()
                AND dbo.Car.CarID = @CarID;
    END;