tnblog
首页

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

109人阅读 2019/3/11 11:19 评论:0 手机浏览 收藏
分类: 数据库


在写有些存储过程的时候需要进行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语句参数化







评价
断剑重铸之日,骑士归来之时
博主搜索
文章
6
评论
8
分类
16
关注
16
文章类别
最新文章
最新评价
{{item.ArticleTitle}}
{{item.BlogName}} : {{item.Content}}