动态SQL

Sample1:
/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
/* Build and Execute a Transact-SQL String with a single parameter value Using EXECUTE Command */
SET @EmpID = 1001
SET @SQLQuery = 'SELECT * from tblEmployees WHERE EmployeeID = ' + CAST(@EmpID AS NVARCHAR(10))
Print @SQLQuery
EXECUTE(@SQLQuery)
 
Sample2:
* Using sp_executesql - Example 1.1 */
/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* Build and Execute a Transact-SQL String with a single parameter value Using sp_executesql Command */
SET @EmpID = 1001
SET @SQLQuery = 'SELECT * from tblEmployees WHERE EmployeeID = @EmpID'
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID
 
 
Sample3:
ALTER Procedure sp_EmployeeSelect
 @EmployeeName NVarchar(100),
 @Department NVarchar(50),
 @Designation NVarchar(50),
 @StartDate DateTime,
 @EndDate DateTime,
 @Salary Decimal(10,2),
 @Total INT OUTPUT
 
    
AS
 
 Set NoCount ON
 
 Declare @SQLQuery AS NVarchar(4000)
 Declare @ParamDefinition AS NVarchar(2000)
 
 Set @SQLQuery = 'Select * from tblEmployees where (1=1) '
 
 If @EmployeeName Is Not Null
      Set @SQLQuery = @SQLQuery + ' And (EmployeeName = @EmployeeName)'
 If @Department Is Not Null
      Set @SQLQuery = @SQLQuery + ' And (Department = @Department)'
 
 If @Designation Is Not Null
      Set @SQLQuery = @SQLQuery + ' And (Designation = @Designation)'
 
 If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
      Set @SQLQuery = @SQLQuery + ' And (JoiningDate BETWEEN @StartDa