MSSQL stored procedure realizes stitching SQL's attention point

The problem I ran into yesterday was the execution of a change in the field state of a different table based on the variable tablename. Because of the server, I couldn't write SQL directly at the data access layer, so I had to pull it out and put it in the stored procedure.

There is a problem here, I spent a long time to good grasp!

is actually a very simple SQL statement:

Update table1 Set field1=value1,field2 = value2 WHERE id = ID

What did I look like? Everyone and see:

 　　 Declare @tableName nvarchar( -),@field1 int,@field2 nvarchar,@id int 　　 Declare @sql nvarchar(Max)Set @sql = 'Update'+@tableName+'Set field1='+@field1+', field2='+@field2+'where id='+@id 　　 exec @sql

Students who have experience in this area must know that this is obviously wrong, SQL will report an exception, said nvarchar converted to int type

So what is the exact value of this error? In fact, when splicing SQL, this @sql if a string type, all the variables must also be represented by a string, I above the @field1 and @id is an int, so to convert to nvarchar type, and a variable of type nvarchar (field) must be enclosed in single quotation marks; Note: single quotes in SQL are represented by two single quotes

So after modification, the correct splicing of the SQL code is:

　　

Set @sql='Update'+@tableName+ 'Set field1='+cast(@field1  as varchar)+', field2=" "+@field2+" "where id='+CAST(@id  as varchar)

This problem is very simple, but because my knowledge of SQL is very insufficient, so here record to consolidate their

　　

MSSQL stored procedure realizes stitching SQL's attention point