Stored procedure update specific column by parameter

I am developing a stored procedure to update specific columns using parameters.

For example:

CREATE PROCEDURE save_proc
(
   @userid varchar(5),
   @column varchar(10),
   @data varchar(50)
)
AS
BEGIN
    UPDATE MyProfile 
    SET @column = @data 
    WHERE userid = @userid

The following is my code that I've tried but didn't work. Is it possible to do such an update by updating specific columns through parameters? Thanks

You can build up a query in your stored procedure and execute.

CREATE PROCEDURE save_proc
(
   @userid varchar(5),
   @column varchar(10),
   @data varchar(50)
)
AS
BEGIN
    exec sp_executesql N'UPDATE MyProfile SET ' + @column + '=''' + @data + ''' WHERE userid = ''' + @userid + ''''
END

The method may lead to security concern however.

Update column based on input variable in stored procedure, I think your stored procedure looks great! The only line that had me scratching my head is this one: Execute ('UPDATE [TblActionsOldest] SET ' + @vFieldName  sql - Stored procedure update specific column by parameter - Stack Overflow I am developing a stored procedure to update specific columns using parameters. For example: CREATE PROCEDURE save_proc (@userid varchar(5), @column varchar(10), @data varchar(50)) AS

Its doable but i would avoid doing it.... you code should be like below:

DECLARE @column varchar(10)
DECLARE @data varchar(50)
UPDATE DummyTable 
SET 
col1 = CASE WHEN @column = 'Col1' THEN @data ELSE col1 END ,
col2 = CASE WHEN @column = 'Col2' THEN @data ELSE col2 END
where userid =  @userid
.
.
.

Hope that this is what you are looking for

Stored procedure update specific column by parameter, I am developing a stored procedure to update specific columns using parameters​. For example: CREATE PROCEDURE save_proc ( @userid varchar(5),  One way would be to do an ISNULL function for the datasource in which the first argument to the ISNULL function is your procedure argument and the second argument is the original column value. Under circumstances in which the parameter is null the value will be set to the original column value; however, it also might not meet your particular needs.

Using a CASE statement causes all columns identified to once again be updated; this would invoke a reindexing for some indexes upon columns wherein that change, even if the same value, occurs. Waste of execution updates on server.

An IF statement is best option for single value, especially if value is NOT the same type as the column datatype.

IF(@fieldName IS NULL) SELECT @fieldName = ''
IF(@updateValue IS NULL) SELECT @updateValue = ''

DECLARE @myCount int
SELECT @myCount = count(*) FROM [dbo].[myTable] WHERE @inventoryID =  @updateInventoryID

--if no valid data to update,  return -1 for noUpdate
IF(0 = @updateInventoryID 
   OR 0 = @myCount 
   OR '' = @fieldName 
   OR '' = @updateValue)
RETURN -1


BEGIN TRAN

 IF('notes' = @fieldName)  
    UPDATE [dbo].[myTable] 
    SET [notes] = @updateValue, [modifyDate] = GETDATE() 
    WHERE [inventoryID] = @updateInventoryID


 IF('reorderPoint' = @fieldName)  
 BEGIN  
   SET @newValueInt = CONVERT(int, @updateValue)
   UPDATE [dbo].[myTable] 
    SET [reorderPoint] = @newValueInt, [modifyDate] = GETDATE() 
    WHERE [inventoryID] = @updateInventoryID
 END  


 IF('safetyStock' = @fieldName)  
 BEGIN  
   SET @newValueInt = CONVERT(int, @updateValue)
   UPDATE [dbo].[myTable] 
    SET [safetyStock] = @newValueInt, [modifyDate] = GETDATE() 
    WHERE [inventoryID] = @updateInventoryID
 END  


 IF('quantityOnHand' = @fieldName)  
 BEGIN  
   SET @newValueInt = CONVERT(int, @updateValue)
   UPDATE [dbo].[myTable] 
    SET [quantityOnHand] = @newValueInt, [modifyDate] = GETDATE() 
    WHERE [inventoryID] = @updateInventoryID
 END


 IF('totalLeadTimeDays' = @fieldName)  
 BEGIN  
   SET @newValueInt = CONVERT(int, @updateValue)
   UPDATE [dbo].[myTable] 
    SET [totalLeadTimeDays] = @newValueInt, [modifyDate] = GETDATE() 
    WHERE [inventoryID] = @updateInventoryID
 END

 IF('stockTakeETADate' = @fieldName)  
 BEGIN  
   SET @newValueDatetime = CONVERT(datetime, @updateValue)
   UPDATE [dbo].[myTable] 
    SET [stockTakeETADate] = @newValueDatetime, [modifyDate] = GETDATE() 
    WHERE [inventoryID] = @updateInventoryID
 END

 COMMIT

A Try/Catch is advisable

Stored Procedure for updating dynamic columns in a table, create a stored procedure that will update a specific column to a new value. I the @columname and @newvalue to be the parameters, and an  UPDATE Stored Procedure in SQL Server with WHERE Clause In this example, we will show you how to use the WHERE Clause, along with the UPDATE Statement inside the Stored procedure. From the below code snippet, you can see that the procedure will update the Last Name as Gateway Tutorial.

Select, Insert, Update, Delete Using Stored Procedure in SQL Server , Select, Insert, Update, Delete Using Stored Procedure in SQL Server 2008 and select rows from a table, depending on the statement type parameter. Now for insert, we fill the data in values in the required fields. a single stored procedure to perform all operations using a single SP in SQL Server. If you want to use stored procedures, the simplest is probably to have a stored procedures that accepts parameters for all columns and then update all columns. Typically the client layer has earlier retreved all columns anyway, so you have that data.

SQL QUERY PROCEDURE-COLUMNS Statement, The SQL QUERY PROCEDURE-COLUMNS statement returns a result set that as well as the columns in the result set, if any, for a specified stored procedure of an of the stored procedure or procedures for which the parameter and column​  I am almost finished changing a cursor-based stored procedure over to set-based. Almost, because I have only one thing left to figure out. They use a stored procedure called GetSequence to query a table, update it with a new sequence number (old + 1) and return the new sequence number value. This wasn't an issue when they used cursors because

Data Model/Tables, Views and Stored Procedures, Click Tables, Views or Stored Procedures in the Middle Panel. (Fig 66 Data Model - Data Source Grid and Column Grid A stored procedure with one (or more) input parameter might have different output schemas based on the value of that  You could update the rows after executing the stored procedure with the parameters you supplied for execution. You could add or modify default constraints for the "parameter" columns. I can think of other alternatives - all of which require more than your single statement. You could, of course, change the stored procedure to return the parameters as a part of the resultset - but it seems you have passed over this approach. – SMor Jan 10 at 18:39

Comments
  • Seems like there's a missing END after BEGIN?
  • No, that syntax (a variable instead of a column name) is not valid. Instead, you can use dynamic SQL (building the update statement as a string then executing it). Is this SQL Server, or some other product?
  • As Blorgbeard said, you would need to use dynamic SQL (which might have significant disadvantages, especially vulnerability to being hacked). See example at stackoverflow.com/questions/12846743/…
  • Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely.
  • UPDATE tbl SET @colname = @value WHERE keycol = @keyval
  • What if user wants to insert NULL value? EXEC save_proc '5', 'col', NULL
  • It does not work anyway. You could pass to EXEC value/variable/DEFAULT as argument and concatenation is expression.
  • this causes all columns identified to once again be updated; this would invoke a reindexing for some indexes upon columns wherein that change, even if the same value, occurs. An IF statement is best option for single value, especially if value is NOT the same type as the column datatype. `BEGIN TRAN IF(@columnToUpdate == 'columnA') THEN UPDATE [myTable] SET @columnToUpdate - @newValue IF(@columnToUpdate == 'columnA') THEN BEGIN DECLARE @newValueINT int UPDATE [myTable] SET @columnToUpdate - @newValue