exec sp_executesql的具体应用

ALTER

PROCEDURE [dbo].[PRC_ORG_DropDownList_dgsu_Result_By_Table]

@dept_id

VARCHAR(8)=NULL,

@group_id

VARCHAR(8)=NULL,

@section_id

VARCHAR(8)=NULL,

@unit_id

VARCHAR(8)=NULL,

@tableName

VARCHAR(50)

AS

IF @dept_id ='0'SET @dept_id =NULL

 

IF @group_id ='0'SET @group_id =NULL

 

IF @section_id ='0'SET @section_id =NULL

 

IF @unit_id ='0'SET @unit_id =NULL

BEGIN

DECLARE @sql nVARCHAR(300)SET @sql =N' SELECT * FROM '+@tableName+' where '+' ((@dept_id is null) or ( dept_id = @dept_id )) and '+' ((@group_id is null) or ( group_id = @group_id )) and '+' ((@section_id is null) or ( section_id = @section_id )) and '+' ((@unit_id is null) or ( unit_id = @unit_id ))'

 

exec sp_executesql@sql,N'@dept_id VARCHAR(8), @group_id VARCHAR(8) ,@section_id VARCHAR(8) ,@unit_id VARCHAR(8)',

@dept_id

,@group_id,@section_id,@unit_id

END