CODEWITHSUNDEEP
sql-servert-sql
MohdO
MohdO

Reputation: 21

How to allow users to only update certain fields

I have this store procedure and I lets assume the user want to update some fields not all of it, what should I add on the update section

CREATE PROCEDURE [dbo].[helpChainAllCRUD] 
    @action char(1),
    @lineId char(2),
    @lineShift smallint = NULL, 
    @sequence smallint = NULL,
    @Role VARCHAR(32) = NULL,
    @radioChannel VARCHAR(16)= NULL,
    @officePhone VARCHAR(16)= NULL,
    @cellPhone VARCHAR(16)= NULL
as

IF(@action = 'I')
    BEGIN TRY
        BEGIN TRAN
            INSERT INTO [dbo].[tbl_helpChain] (lineId,lineShift,sequence,Role,radioChannel,officePhone,cellPhone)
            VALUES (@lineId ,@lineShift,@sequence,@Role,@radioChannel,@officePhone,@cellPhone)
        COMMIT
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT >0
            ROLLBACK
    END CATCH

IF(@action = 'U')
    BEGIN TRY
        BEGIN TRAN
            UPDATE [dbo].[tbl_helpChain] SET lineShift=@lineShift,sequence=@sequence,Role=@Role,radioChannel=@radioChannel,officePhone=@officePhone,cellPhone=@cellPhone WHERE lineId=@lineId
            COMMIT
        END TRY
    BEGIN CATCH
        IF @@TRANCOUNT >0
            ROLLBACK
    END CATCH

IF(@action = 'D')
    BEGIN TRY
        BEGIN TRAN
        Delete from [dbo].[tbl_helpChain] WHERE lineId=@lineId
        COMMIT
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT >0
            ROLLBACK
    END CATCH

IF(@action = 'S')
    Begin
        select lineId,lineShift,sequence,Role,radioChannel,officePhone,cellPhone from [dbo].[tbl_helpChain]  
    end

GO

Upvotes: 0

Views: 69

Answers (3)

MohdO
MohdO

Reputation: 21

UPDATE helpChain

SET

fullName = ISNULL(@fullName,fullName),

lineShift = ISNULL(@lineShift,lineShift),

sequence = ISNULL(@sequence,sequence),

Role = ISNULL(@Role,Role),

radioChannel = ISNULL(@radioChannel,radioChannel),

officePhone = ISNULL(@officePhone,officePhone),

cellPhone = ISNULL(@cellPhone,cellPhone) WHERE lineId = @lineId

Upvotes: 0

Turo
Turo

Reputation: 4914

If i.e. null means don't update you could simply write

SET lineShift = COALESCE(@lineShift,lineShift), ...

or you take another special value and a case-expression

SET lineShift = CASE WHEN @lineShift = -1 then lineShift else @lineShift end,

or you give extra boolean parameters for each column for use in the case-expression

Upvotes: 1

Keith O'Neill
Keith O'Neill

Reputation: 77

I'm sure there are much better solutions, but a quick and easy solution might be to use very ugly dynamic SQL

DECLARE @QueryText nvarchar(max) = 'UPDATE [dbo].[tbl_helpChain] SET '

IF @radioChannel<> NULL
@QueryText = @QueryText + 'RadioChannel=@radioChannel'

EXECUTE SP_EXECUTESQL @QueryText

Upvotes: 1

Related Questions