像下面的分页存储过程,如何将@wherestr
放进去查询语句中?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[UP_SH_User_List]
(@status Int,
@groupid Int,
@usertype Int,
@username nvarchar(20),
@companyname nvarchar(100),
@orderby Int,
@pagesize int,
@pageindex int,
@docount bit)
as
declare @wherestr nvarchar(1000)
declare @sql nvarchar(2000)
if(@status>0)
set @wherestr= ' and Status=' + convert(nvarchar(15),@status); if(@groupid>0)
set @wherestr= @wherestr + ' and GroupID=' + convert(nvarchar(15),@groupid); if(@usertype>0)
set @wherestr= @wherestr + ' and UserType=' + convert(nvarchar(15),@usertype); if(@username!='')
set @wherestr= @wherestr + ' and UserName like %' + convert(nvarchar(100),@username) +'%'; if(@companyname!='')
set @wherestr= @wherestr + ' and CompanyName like %' + convert(nvarchar(100),@companyname) +'%'; set @wherestr=' UserType=' + convert(nvarchar(15),@usertype);
if(@docount=1)
select count(*) from SH_User; else
begin
with temptbl as (SELECT ROW_NUMBER() OVER (ORDER BY UserID desc)AS Row, * from SH_User)
SELECT * FROM temptbl where Row between (@pageindex-1)*@pagesize+1 and (@pageindex-1)*@pagesize+@pagesize
end
放进去查询语句中?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[UP_SH_User_List]
(@status Int,
@groupid Int,
@usertype Int,
@username nvarchar(20),
@companyname nvarchar(100),
@orderby Int,
@pagesize int,
@pageindex int,
@docount bit)
as
declare @wherestr nvarchar(1000)
declare @sql nvarchar(2000)
if(@status>0)
set @wherestr= ' and Status=' + convert(nvarchar(15),@status); if(@groupid>0)
set @wherestr= @wherestr + ' and GroupID=' + convert(nvarchar(15),@groupid); if(@usertype>0)
set @wherestr= @wherestr + ' and UserType=' + convert(nvarchar(15),@usertype); if(@username!='')
set @wherestr= @wherestr + ' and UserName like %' + convert(nvarchar(100),@username) +'%'; if(@companyname!='')
set @wherestr= @wherestr + ' and CompanyName like %' + convert(nvarchar(100),@companyname) +'%'; set @wherestr=' UserType=' + convert(nvarchar(15),@usertype);
if(@docount=1)
select count(*) from SH_User; else
begin
with temptbl as (SELECT ROW_NUMBER() OVER (ORDER BY UserID desc)AS Row, * from SH_User)
SELECT * FROM temptbl where Row between (@pageindex-1)*@pagesize+1 and (@pageindex-1)*@pagesize+@pagesize
end
解决方案 »
- 关于case when 后面的条件怎么写?100分送上
- 数据库 'dvbbs_net' 中拒绝了 CREATE TABLE 权限(动网.net)
- !@_@有点扯淡的查询替换,不知道怎么实现@_@!
- 如何查询编号开头全部是"H"的记录?
- 查询分类与信息的问题,分个分类一条数据!
- 如何开发MSSQL 的SQL语句监视工具?跟MSSQL PROFILE类似的功能
- 编写过程登录数据库查询并插入本地表,问题出在哪?
- sql 游标 报错 FOR UPDATE cannot be specified on a READ ONLY cursor
- 操作excel 选出不同数据,写入数据库???????
- 'SQL语句' 和N'SQL语句'有什么区别?
- 问一select写法问题
- 怎么防止数据库误删除?怎么恢复数据库???
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[UP_SH_User_List]
(@status Int,
@groupid Int,
@usertype Int,
@username nvarchar(20),
@companyname nvarchar(100),
@orderby Int,
@pagesize int,
@pageindex int,
@docount bit)
as
declare @wherestr nvarchar(1000)
declare @sql nvarchar(2000)
if(@status>0)
set @wherestr= ' Status=' + convert(nvarchar(15),@status); if(@groupid>0)
set @wherestr= @wherestr + ' and GroupID=' + convert(nvarchar(15),@groupid); if(@usertype>0)
set @wherestr= @wherestr + ' and UserType=' + convert(nvarchar(15),@usertype); if(@username!='')
set @wherestr= @wherestr + ' and UserName like %' + convert(nvarchar(100),@username) +'%'; if(@companyname!='')
set @wherestr= @wherestr + ' and CompanyName like %' + convert(nvarchar(100),@companyname) +'%'; set @wherestr=' UserType=' + convert(nvarchar(15),@usertype);
if(@docount=1)
exec('select count(*) from SH_User where ' +@wherestr); else
begin exec ('with temptbl as (SELECT ROW_NUMBER() OVER (ORDER BY UserID desc)AS Row, * from SH_User)
SELECT * FROM temptbl where Row between ('+@pageindex+'-1)*'+@pagesize+'+1 and ('+@pageindex+'-1)*'+@pagesize+@pagesize +' and '+@wherestr)
end
SELECT * FROM temptbl where Row between ('+@pageindex+'-1)*'+@pagesize+'+1 and ('+@pageindex+'-1)*'+@pagesize+@pagesize +' and '+@wherestr)
这个是否错了啊?怎么显示不了数据?