1. Classification
SQL dynamic statements must be implemented when executing SQL statements that need to pass in variables;
There are two types of dynamic statements:
(1) Dynamic SQL with return value
(2) No return value dynamic SQL
2. Realize
Suppose there is a student table, with a userid, username two columns, an int with varchar (20)
2.1 Common SQL statement implementations
SELECT from WHERE UserID=123;
2.2 Dynamic SQL implementations
--Query the data based on the ID enteredDECLARE @ID INT DECLARE @SQL VARCHAR( -)SET @SQL = 'SELECT UserID, UserName from stdtable WHERE userid='+@ID+"';EXEC(@SQL)--If you execute the error, use the LTrim () functionSET @SQL = 'SELECT UserID, UserName from stdtable WHERE userid='+LTrim(@ID)+"';
Note: In the above query statement, if you pass in a varchar type of data, you need to add a pair of quotation marks to both sides of the variable
Such as:
SET @SQL = ' SELECT UserID, UserName from stdtable WHERE userid= " " + + " " ' ; EXEC (@SQL)
2.3 Have return value SQL
If you need to return the result of a select to a variable, you would use the following statement:
SET @SQL =N'Select @UserNameTemp = (select UserName from'+@TableName+ 'WHERE userid='+@UserID+')' --Print @TempAASQLEXECsp_executesql@SQLN'@NameTemp Varchar out',@UserNameTemp =@UserName
There is a difference between a return value and no return value:
(1) Execute SQL with no return value is defined as: VARCHAR (1000)
DECLARE @SQL VARCHAR (+)
And no return value is defined as nvarchar (1000)
DECLARE @SQL NVARCHAR (+)
(2) There is a return value of SQL, specific execution, you need to add n before the statement
(3) The system stored procedure must be called sp_executesql execution
Dynamic SQL implementation and considerations (with return value and no return value for dynamic SQL implementation)