Passing a variable to the UPDATE Command

Hello everybody,

I have a problem in passing a variable into the Update command. This is my stored procedure.

CREATE PROCEDURE UpdateTable @.tsID INT, @.UpdateDesc varchar(1000)
AS

DECLARE @.TableNumber INT
DECLARE @.TableName NVARCHAR(100)
DECLARE @.SQL VARCHAR(100)

/* I have to get the name of the table to be updated by first getting the TableId
( an INT field) from a table TS_SUBTASKS and with that the table name from another table TS_TABLES */

SELECT @.TableNumber = TS_SUBTABLEID FROM TS_SUBTASKS
WHERE TS_SUBITEMID = @.tsID

SELECT @.TableName = TS_DBNAME FROM TS_TABLES
WHERE TS_ID = @.TableNumber

/* Which the Table name in the variable @.TableName i have to set one of its
fields, TS_DESCRIPTION to the value passed into the procedure
(@.UpdateDesc) */

SET @.SQL = 'update ' + @.TableName+ 'SET TS_DESCRIPTION = ' + @.UpdateDesc + ' WHERE TS_ID = ' + @.tsID

EXEC(@.SQL)

GO

--But i get this error

Syntax error converting the varchar value 'update USR_HUMAN_RESOURCESSET TS_DESCRIPTION = newdescription WHERE TS_ID = ' to a column of data type int.

It would be very helpful if somebody could throw light on this.
Thanks,
Krishna Murthy.

You should really rethink your approach of using dynamic SQL and this type of design. It requires lot of work in terms of giving permissions to users directly to update the table and coding the dynamic SQL. It is best to use static SQL wherever possible since it is more secure and easier to manage. The errors you are seeing are a result of incorrecly formed UPDATE statement. I believe it is missing some quotes. You should also consider using sp_executesql since that is easier to parameterize like below and you can avoid some of the SQL injection problems with using EXEC incorrectly:

DECLARE @.SQL nvarchar(1000)
SET @.SQL = N'update ' + QUOTENAME(@.TableName) +
N'SET TS_DESCRIPTION = @.Descr WHERE TS_ID = @.ID'
exec sp_executesql @.SQL, N'@.Descr varchar(1000), @.ID int', @.Descr = @.UpdateDesc, @.ID = @.tsID
|||Thank you Mr. Jayachandran, your correction has helped me a great deal. And coming to your suggestion of not taking this approach, I could not find any other way of doing the same. I shall again be very thankful to you if you can suggest a better way of doing this.

Thanks,
Krishna Murthy.|||I meant that you should avoid writing code that relies on dynamic schema elements. They are difficult to manage and code. In your case, you may want to consider generating some of the SP code during the creation at the time of definition of the row that represents a table. This approach also depends on your application and requirements.