How to execute the Update method using a variable to call the table?

33 views Asked by At

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?

0

There are 0 answers