Sunday, March 6, 2011

Saving record using stored procedure


CREATE PROCEDURE [dbo].[spHelpdeskUpdate]
(
    @id int,
    @customername varchar(250),
    @contactno varchar(25),
    @email varchar(50),
    @details varchar(8000),
    @assignedstaff varchar(50),
    @requesttypeid int,
    @statustype int,
    @requestdate datetime,
    @solution varchar(8000),
    @solvedate datetime = null,
    @discontinued bigint,
    @createdby varchar(25),
    @createddate datetime,
    @lastmodifiedby varchar(25),
    @lastmodifieddate datetime = null
)
AS
BEGIN
    SET NOCOUNT ON;

    IF @id= 0
        BEGIN
            INSERT INTO Helpdesk
            (CustomerName,
            ContactNo,
            Email,
            Details,
            AssignedStaff,
            RequestTypeID,
            StatusType,
            RequestDate,
            Solution,
            SolveDate,
            Discontinued,
            CreatedBy,
            CreatedDate,
            LastModifiedBy,
            LastModifiedDate)
            VALUES
            (@customername,
            @contactno,
            @email,
            @details,
            @assignedstaff,
            @requesttypeid,
            @statustype,
            @requestdate,
            @solution,
            @solvedate,
            @discontinued,
            @createdby,
            @createddate,
            @lastmodifiedby,
            @lastmodifieddate)

        SELECT SCOPE_IDENTITY() AS theID

        END
    ELSE
        BEGIN
            UPDATE Helpdesk
            SET
                CustomerName=@customername,
                ContactNo=@contactno,
                Email=@email,
                Details=@details,
                AssignedStaff=@assignedstaff,
                RequestTypeID=@requesttypeid,
                StatusType=@statustype,
                RequestDate=@requestdate,
                Solution=@solution,
                SolveDate=@solvedate,
                Discontinued=@discontinued,
                CreatedBy=@createdby,
                CreatedDate=@createddate,
                LastModifiedBy=@lastmodifiedby,
                LastModifiedDate=@lastmodifieddate
            WHERE ID = @id
            SELECT @id AS theID

        END
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON


No comments:

Post a Comment