SqlServer存储过程拼接sql语句，分页

在写有些存储过程的时候需要进行sql的拼接，然后在使用exec函数执行sql语句

注意字符串的拼接，看下面一段存储过程的sql语句拼接

create procedure proc_page(@page int,@row int,@username varchar,@number varchar)
as 
begin
  --定义变量
  declare @sql nvarchar(512);
         
  set @sql='select * from (
       select *,ROW_NUMBER() over(order by id) as rownumber  from  Users 
       )temp where  rownumber between '+(@page-1)*@row+1 +' and '+@page*@row

 exec(@sql)

end;

执行会报错：

 exec proc_page 1,5,'',''

这是因为在sqlserver数据库中,字符串和数字类型相加时不会自动转换，需要手动转换，

可以使用cast函数进行转换

alter procedure proc_page(@page int,@row int,@username varchar,@number varchar)
as 
begin
  --定义变量
  declare @wheresql nvarchar(512),
          @sql nvarchar(512);
         
  set @wheresql = '';
  set @sql='select * from (
       select *,ROW_NUMBER() over(order by id) as rownumber  from  Users 
       )temp where  rownumber between '+cast(((@page-1)*@row)+1 as varchar)+' and '+cast(@page*@row as varchar);

 exec(@sql) --注意这里要加括号,而不是exec @sql，后者是执行存储过程而不是sql语句

end;

然后执行就能成功了

下面可以进行分页条件的拼接

alter procedure proc_page(@page int,@row int,@username nvarchar(64),@number nvarchar(64))
as
begin
  
  --拼接sql变量
  declare @sql nvarchar(512);
  --动态条件变量
  declare @wheresql nvarchar(128);
  --动态参数化变量
  declare @params nvarchar(64)
  set @wheresql =''

  if(@username is not null and @username!='')
  begin
    set @wheresql+=' and username= @username';
  end;
  if(@number is not null and @number!='')
  begin
    set @wheresql+=' and number= @number';
  end;

   set @sql='select * from (
       select *,ROW_NUMBER() over(order by id) as rownumber  from  Users where 1 = 1 '+@wheresql
       +')temp where  rownumber between '+cast(((@page-1)*@row)+1 as varchar)+' and '+cast(@page*@row as varchar);
   
   --给动态拼接的sql提供参数化
   set @params = '@username nvarchar(64),@number nvarchar(64)';
   exec sp_executesql  @sql,@params,
   @username=@username,
   @number = @number
end

执行的时候可以进行两个条件的自由组合

同时作用：

exec proc_page 1,5,'吕布','NS002'

作用一条：

exec proc_page 1,5,'','NS002'

这里主要是要注意，在存储过程中拼接sql语句为了安全也是需要使用参数化的，

但是exec执行sql语句是无法参数化的，所以需要使用sp_executesql来动态参数化

其实很重要的代码就是：

--给动态拼接的sql提供参数化
   set @params = '@username nvarchar(64),@number nvarchar(64)';
   exec sp_executesql  @sql,@params,
   @username=@username,
   @number = @number

可以动态给拼接的sql语句参数化