sql存储过程动态执行sql

1.动态执行sql
create proc proc_GetWorkHourByEmpId
@key1 varchar(100), --关键字
@user_group1 varchar(100),--用户分组
as
begin
declare @SqlSelect nvarchar(2000),@ParmDefinition nvarchar(max);
set @SqlSelect=' select * from User where 1=1 ' --sql语句
set @ParmDefinition=N'@key nvarchar(10),@user_group nvarchar(50) ';  --参数声明

--条件判断
if(@key1 is not null and @key1!='')
begin
set @SqlSelect=@SqlSelect+' and (Emp_Name like @key)'
end

if(@user_group1 is not null and @user_group1!='')
begin
set @SqlSelect=@SqlSelect+' and User_Grouping like @user_group '
end

--执行sql

EXEC sp_executesql @SqlSelect,@ParmDefinition,@key=@key1,
@user_group=@user_group1
end

2.动态执行sql ,有返回值(output必须加)

--根据录入人获取工时统计信息 zxk
ALTER proc [dbo].[proc_GetWorkHourByProject]
@key1 varchar(100),

@totalCount1 int output--返回总条数

as
begin
declare @SqlSelect nvarchar(2000),@ParmDefinition nvarchar(max);
set @ParmDefinition=N'@totalCount int output,@key varchar(100)';

set @SqlSelect='select @totalCount=count(*) from User where 1=1 '
if(@key1 is not null and @key1 !='')
begin
set @SqlSelect=@SqlSelect+' and name like @key '
end

 

EXEC sp_executesql @SqlSelect ,@ParmDefinition,@key=@key1,@totalCount=@totalCount1 output

end

 

转载于:https://www.cnblogs.com/zxking/p/8656149.html