Dynamic SQL implementation and considerations (with return value and no return value for dynamic SQL implementation)

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)