Sql-server – update stored proc ony fields that are passed to the proc and leaving others alone

sql-server-2005stored-procedures

i have a stored proc that does a simple update (see below).

i want to update only fields passed and not the ones that are null. that is to say if i dont get a parm value for somethign i dont want to update the table data to null, but rather just leave what the current value is.

can this be done in a sql update statement such as this?

ALTER PROCEDURE [dbo].[upd_MessageDetail]  
    @MessageId INT,  
    @IsDraft INT,  
    @IsPreviewed INT,  
    @IsRead INT,  
    @IsFlagged INT,  
    @IsDeleted INT,  
    @IsArchived INT    
AS  
BEGIN  


        UPDATE MessageDetail   
        SET 
            IsDraft = @IsDraft,  
            IsPreviewed = @IsPreviewed,  
            IsRead = @IsRead,  
            IsFlagged = @IsFlagged,  
            IsDeleted = @IsDeleted,  
            IsArchived = @IsArchived   
        WHERE MessageId = @MessageId

Best Answer

Something like below if I understand you correctly.

UPDATE MessageDetail   
        SET 
            IsDraft = ISNULL (@IsDraft, IsDraft), 
            IsPreviewed = ISNULL (@IsPreviewed,  IsPreviewed),
            IsRead = ISNULL (@IsRead, IsRead), 
            IsFlagged = ISNULL (@IsFlagged, IsFlagged), 
            IsDeleted = ISNULL(@IsDeleted, IsDeleted)
            IsArchived = ISNULL(@IsArchived, IsArchived)
        WHERE MessageId = @MessageId

Related Solutions

Sql-server – Which problems arise, declaring the size of all varchar parameters as max in stored proc

Since your talking about the proc params:

CREATE PROCEDURE myProc
  @var1 varchar(30),
  @var2 varchar(max)
AS
...

If the parameters are defined as the correct length, SQL will throw an error when calling the procedure. If you always define as (max) then you'll have to manually handle passed parameters that are too long for the destination table column before any processing in the proc happens.

So in a nutshell, more work on your end since you'll have to add error handling to the proc you didn't need before.

PostgreSQL – Differences Between Stored Procedures and Stored Functions

Officially, PostgreSQL only has "functions". Trigger functions are sometimes referred to as "trigger procedures", but that usage has no distinct meaning. Internally, functions are sometimes referred to as procedures, such as in the system catalog pg_proc. That's a holdover from PostQUEL. Any features that some people (possibly with experience in different database systems) might associate with procedures, such as their relevance to preventing SQL injections or the use of output parameters, also apply to functions as they exist in PostgreSQL.

Now, when people in the PostgreSQL community talk about "stored procedures" or "real stored procedures", however, they often mean a hypothetical feature of a function-like object that can start and stop transactions in its body, something that current functions cannot do. The use of the term "stored procedure" in this context appears to be by analogy to other database products. See this mailing list thread for a vague idea.

In practice, however, this distinction of function versus procedure in terms of their transaction-controlling capabilities is not universally accepted, and certainly many programmers without database bias will take a Pascal-like interpretation of a procedure as a function without return value. (The SQL standard appears to take a middle ground, in that a procedure by default has a different transaction behavior than a function, but this can be adjusted per object.) So in any case, and especially when looking at questions on Stack Exchange with a very mixed audience, you should avoid assuming too much and use clearer terms or define the properties that you expect.

Best Answer

Related Solutions

Sql-server – Which problems arise, declaring the size of all varchar parameters as max in stored proc

PostgreSQL – Differences Between Stored Procedures and Stored Functions

Related Question