Splicing SQL statements during storage

In many cases, we need to re-organize the SQL statement in the stored procedure using parameters. The SQL statement concatenated In the stored procedure is only a string and will not be executed directly, so add an Execute Command to execute it. See the following demo for details:Code:

 

Code:

 

 
Set ansi_nulls onset quoted_identifier ongo -- ============================== ============ -- Author: YY -- create Date: 2012-1-17 -- Description: example of SQL String concatenation in a stored procedure -- ============================================== ============= create procedure [DBO]. [Test] @ filename varchar (10), -- field name @ operator varchar (1), -- operator @ filevalue varchar (10) -- field value as declare @ tempsql varchar (100) -- temporarily store the SQL statement beginset @ tempsql = 'select * from comment where' + @ filename + @ operator + char (39) + @ filevalue + char (39) -- concatenate the SQL string, char (39) is single quotes execute (@ tempsql) -- execute SQL string end
 

Test:

 

 

 
Execute test 'newsid ','> ', 4
 

 

Here I will explain "alterprocedure [DBO]. [Test] What is the difference between the code from "as" to "begin" and the code from "as" to "begin"? SQL beginners like me should have questions: why should @ tempsql be defined between "as" and "begin? Because "as" to "begin" are defined as temporary variables, the front must be added with declare, which is the same as the use of common variables in other languages; and "alter procedure [DBO]. [Test] "to" as "defines the required parameters passed in when a stored procedure is called. values must be assigned during the call. Declare cannot be added, it can be understood as a character constant. Once a value is assigned during the call, it cannot be changed. In the above example, it is wrong to write a statement similar to @ filename = 'xxx. Because @ tempsql is only used to accept the temporary variables of the SQL statement, there is no initial value, but the value must be accepted, so it must be defined between "as" and "begin.