Update column based on input variable in stored procedure

Question

The purpose of the below code is to update a column based on the name of the field name sent from the .NET code. This allows one piece of code to handle multiple rows when a user is only adding/updating one at a time.

I have been using Stored Procedures for a while but normally just Add/Update but not using variable field names. All tips appreciated.

USE DB
GO
/****** Object:  StoredProcedure [dbo].[spActionUpdateOldestDate]    Script Date: 04/02/2014 14:24:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUpdateViaFieldName] 

-- spActionUpdateOldestDate '1234','date','field'
-- Add the parameters for the stored procedure here
@AlphaNumbericalRef nvarchar(50)            
,@vValue nvarchar(MAX) 
 ,@vFieldName varchar(MAX)

AS
BEGIN

-- add selection for courseID etc.. here
Execute ('UPDATE [TblActionsOldest] SET ' + @vFieldName + ' = ''' + @vValue + ''' WHERE RefID = ''' + @AlphaNumbericalRef+ '''')

END

Answer 1

I think your stored procedure looks great!

The only line that had me scratching my head is this one:

Execute ('UPDATE [TblActionsOldest] SET ' + @vFieldName + ' = ''' + @vValue + ''' WHERE RefID = ''' + @AlphaNumbericalRef+ '''')

I'm guessing those apostrophes are escape characters for asp.net but if that's not the case I would discard them.

Nice work!

Answer 2

The only option that you have if you don't want to use all the apostrophes is to create stored procedures for updating each column and then create an outside stored procedure that you provide the column name to and it picks the right stored procedure based on the parameters that you pick, you can still send

@AlphaNumbericalRef nvarchar(50)            
,@vValue nvarchar(MAX)

through to the nested Stored Procedures.

I am thinking that the performance may be better if you do it this way as opposed to the Dynamic SQL, because the Server can optimize the Stored Procedures whereas the Dynamic SQL leaves the Server in the dark about what is going to happen and doesn't give it a chance to plan the query, with an UPDATE (or a DELETE) you want to give the Server enough information to plan these things out, especially if they are going to happen many times on a large scale.

---

These are links that I found with a quick Google Search( of the title of this question "Update column based on input variable in stored procedure" ) I landed first on a StackOverflow question

1. Answer to This Question
2. Dynamic SQL
3. Query Plans