Notes on SQL Server Stored Procedure Implementation and splicing, SQL Server Stored Procedure

Notes on SQL Server Stored Procedure Implementation and splicing, SQL Server Stored Procedure

The problem I encountered yesterday is to execute a variable tableName to change the field status of different tables. For server reasons, I cannot write SQL directly at the data access layer, so I had to leave it out and put it in the stored procedure.

There was a problem here. It took me a long time to get it done!

　　It is actually a simple SQL statement:

　　update table1 set field1=value1,field2 = value2 where id = id

　　What did I write? You can also see:

declare @tableName nvarchar(50),　　　　　　@field1 int,　　　　　　@field2 nvarchar,　　　　　　@id int　　declare @sql nvarchar(max)　　set @sql = 'update '+@tableName+' set field1= '+@field1+',field2= '+@field2+' where id='+@id　　exec @sql 

Those who have experience in this field must know that this writing is obviously wrong. SQL reports an exception, saying that nvarchar cannot be converted to the int type.

So what is the specific value of this error? In fact, when splicing SQL statements, if @ SQL is a string type, all variables must be represented by strings. The @ field1 and @ id above are int, therefore, variables (fields) of the nvarchar type must be enclosed in single quotes. Note: in SQL, single quotes are expressed in two single quotes.

　　Therefore, after modification, the correct SQL code is:

set @sql='update '+@tableName+ ' set field1='+cast(@field1 as varchar)+',field2='''+@field2+''' where id='+CAST(@id as varchar) 

This problem is actually very simple, but I am not familiar with SQL, so I will record it here to consolidate myself.

The preceding section describes how to implement the SQL Server Stored Procedure and how to splice SQL statements. I hope to help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!