动态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 @StartDate AND @EndDate)'

 If @Salary Is Not Null
      Set @SQLQuery = @SQLQuery + ' And (Salary >= @Salary)'
 
 Set @ParamDefinition = '@EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)'

 SELECT @Total=COUNT(*) FROM dbo.tblEmployees
 Exec sp_Executesql  @SQLQuery,
    @ParamDefinition,
    @EmployeeName,
    @Department,
    @Designation,
    @StartDate,
    @EndDate,
    @Salary
    
 If @@ERROR <> 0 GoTo ErrorHandler
 Set NoCount OFF
 Return(0)
 
ErrorHandler:
 Return(@@ERROR)
GO

 

DECLARE @i int
EXEC sp_EmployeeSelect 'John Smith', NULL, NULL, NULL, NULL, NULL,@i OUTPUT
SELECT @i

 

 

http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-2