Sql-server – stored procedure update retain values

sql serverstored-proceduresupdate

In SQL Server, when using optional parameters, the only way to have default values is using @param = NULL upon creating.

Is there a way for the stored procedure to know that the application will only update specific columns and the remaining columns will retain the original values?

Here is my example:

Data: col1 = "AA" col2 = "BB" col3 = "CC"

Application update inside stored procedure: UPDATE table SET col3 = "C", col ="B" WHERE id = 1

After update, value of col1 will remain.

I hope my explanation is clear enough 🙂

Best Answer

If I understood correctly, if stored procedure is called with @vCol1 = 'AA', @vCol2 = 'BB' and @vCol3 = NULL then you want to update only Col1 and Col2 keeping the Col3 value intact. Then you can go with the following solution. I agree its not the best one but it will get work done in simple manner.

UPDATE table
SET col1 = ISNULL(@vCol1, col1)
  , col2 = ISNULL(@vCol2, col2)
  , col3 = ISNULL(@vCol3, col3)
WHERE id = 1

Related Solutions

Sql-server – Restrict update on certain columns. Only allow stored procedure to update those columns

SQL Server allows column-level permissions. Just for example:

GRANT UPDATE ON dbo.Person (FirstName, LastName) TO SampleRole;
DENY UPDATE ON dbo.Person (Age, Salary) TO SampleRole;

Sql-server – Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler

I'll bite the bullet and tell you that such a trace cannot be set up, because it is not the [perceived] purpose of traces. I have always done it this way:

WHILE (@@FETCH_STATUS = 0) AND
( @About80MoreBooleanExpressionsHere)
BEGIN /* 1 */
-- about 700 lines of logic, math and if-parameter-this-then-that
-- stuff omitted
INSERT InnerProcedureCallWithinLoop__TraceTable
VALUES (@from_locn, @About53PARAMSOMITTED
EXEC @ConsiderItem =
InnerProcedureCallWithinLoop
@from_locn,
@About53PARAMSOMITTED,
...

If I know that it is only ever called from one location. Otherwise, I do it in the callee instead of the caller.

ALTER PROC InnerProcedureCallWithinLoop
    @from_locn int,
    @About53PARAMSOMITTED ...
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
INSERT InnerProcedureCallWithinLoop__TraceTable VALUES (@from_locn, @prm2, @prm3....
--- rest of proc

This is obviously different from using a trace, which is able to capture events even if they started and never finished (faulty parameters, rolled back transactions). If that is your problem, you need to look at CLR or email methods to externalise the captured output.

Best Answer

Related Solutions

Sql-server – Restrict update on certain columns. Only allow stored procedure to update those columns

Sql-server – Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler

Related Question