1

I'm trying to create a procedure in SQL Server to allow me to update a column on a table.
However, I want the table name to come from a variable instead of being given by me, but I get an error message. Here's what I have so far:

I have a form in an app with several fields and I have a table (FormFieldsTable) in SQL where I have the name of the field, table where it belongs... among other info about those fields. On the procedure, I have the @inputFieldID, @inputRequestID and @inputUpdatedField as inputs. Then, I declare the field name and table and set them according to the table FormFieldsTable. After, I declare the variable TableID which is the name of the ID column for each table, that is always the table name followed by 'ID', and the variable @TableName which is a concatenation between the Schema and the table name of that field.

@inputFieldID int,
@inputRequestID int,
@inputUpdatedField nvarchar(100)=null

AS
BEGIN

DECLARE @FieldTable nvarchar(50)
DECLARE @TableIDName nvarchar(50) 
DECLARE @FieldName nvarchar(100)
DECLARE @TableName nvarchar(50)

SET @FieldTable = (SELECT FieldTable FROM FormFieldsTable WHERE FormFieldsTableID=@inputFieldID)
SET @TableIDName = CONCAT(@FieldTable,'ID') 
SET @FieldName = (SELECT REPLACE(FieldName,' ','') FROM FormFieldsTable WHERE FormFieldsTableID=@inputFieldID)
SET @TableName = CONCAT('Schema.',@FieldTable)

After all of this, I want to do an Update method on the table of that field, so I do the following:

UPDATE @TableName
SET @FieldName=@inputUpdatedField
WHERE @TableIDName=(SELECT @TableIDName FROM Schema.Request WHERE RequestID=@inputRequestID)

The problem is that I get an error message on the UPDATE @TableName saying "Must declare the table variable @TableName". I tried to create a @tempTable but then I still need to update the correct table so I don't know how to fix this.

Can you help me?

Thom A
  • 88,727
  • 11
  • 45
  • 75
risan
  • 11
  • 2
  • 1
    `UPDATE @TableName` doesn't mean update the table with the name of the value within the *scalar* variable, `@TableName`, it means update the *table variable* `@TableName`. Why do you need to parametrise the table name here? Generally this is a sign of a design flaw. – Thom A Aug 16 '21 at 11:29
  • Also `WHERE @TableIDName=(SELECT @TableIDName...` will always be true, unless `@TableIDName` has the value `NULL`. – Thom A Aug 16 '21 at 11:30
  • In order to dynamically reference SQL objects within a script, you will need to use (as the name suggests) [dynamic SQL](https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/). Whilst there are instances where this is the preferred or even only option, it is usually a sign of bad design choices. – iamdave Aug 16 '21 at 11:42
  • 1
    Where am I "browbeating" @iamdave ? I explain *why* they get the error they do, and I am asking the OP *why* they want to do what they want to do, because very likely it's a design flaw; if we find out the *why* we can likely push them in the right direction. They are clearly new to SQL, as they're trying to write it like a scripting language, and so educating them now to not implement such design flaws is by far the best thing we could be doing here. The only condescending language I see here is from you, towards me... – Thom A Aug 16 '21 at 11:48
  • @iamdave Problem here is we don't know know *why* OP wants to do this, so it's impossible to say if it is actually a design flaw or what that flaw might be. We have no context here other than a question "how to update a table dynamically" and all that can be said is that it is *usually* needed because of a design flaw. At a guess, either there is serious denormalization going on here, or there is an attempt to build a ORM mapper within SQL itself (which is a bad idea, it should be built in the native client language). Happy to help OP further if more detail is given. – Charlieface Aug 16 '21 at 11:48
  • @Charlieface Granted, but the SO Q&A format is rarely going to actually be able to provide a proper environment in which to offer the 'best' solution to the given problem because there simply isn't the space or capacity to adequately explain a full context. We get narrow snippets of situations, even in the most comprehensive of questions. Providing answers to the questions as asked - with relevant caveats and warnings as deemed appropriate - is far preferable to attempts at consultancy within a 600 character comment. – iamdave Aug 16 '21 at 11:54
  • Indeed I am new to SQL... I wasn't aware that `Update @TableName` would mean update the variable instead of first, "substituting" the value of that variable for what I did on the SET/SELECT and only then Update... Thank you for that information I will take a look at dynamic SQL for sure! @Charlieface I will give it a try and let you all know. Thank you all so much!! – risan Aug 16 '21 at 12:17
  • @Charlieface the link you sent was very useful. I was able to do want I wanted. Thank you a lot! – risan Aug 17 '21 at 09:41

0 Answers0