sqlserver搜索分页并返回总数

1，很多时候我们需要对一些前端的UI通过json渲染数据，这样才还对接，那么通过sql可以很好的放回对应的数据，然后把数据转换成对应的类型就好了

先上代码

1，创建表

CREATE TABLE [dbo].[Wsyj_User](
	[Uid] [int] IDENTITY(1,1) NOT NULL,
	[Account] [varchar](50) NULL,
	[AccountPwd] [varchar](50) NULL,
	[Name] [varchar](50) NULL,
	[Sex] [int] NULL,
	[Phone] [varchar](50) NULL,
	[Mail] [varchar](50) NULL,
	[DoWork] [varchar](50) NULL,
	[WorkYear] [int] NULL,
	[WorkAddress] [varchar](50) NULL,
	[Card] [varchar](50) NULL,
	[IsDel] [int] NULL,
	[datetime] [datetime] NULL,
	[Organization] [varchar](50) NULL,
 CONSTRAINT [PK_Wsyj_User] PRIMARY KEY CLUSTERED 
(
	[Uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Wsyj_User] ADD  CONSTRAINT [DF_Wsyj_User_IsDel]  DEFAULT ((0)) FOR [IsDel]
GO
ALTER TABLE [dbo].[Wsyj_User] ADD  CONSTRAINT [DF_Wsyj_User_datetime]  DEFAULT (getdate()) FOR [datetime]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'账号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Wsyj_User', @level2type=N'COLUMN',@level2name=N'Account'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Wsyj_User', @level2type=N'COLUMN',@level2name=N'AccountPwd'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Wsyj_User', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0-男，1-女' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Wsyj_User', @level2type=N'COLUMN',@level2name=N'Sex'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'电话' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Wsyj_User', @level2type=N'COLUMN',@level2name=N'Phone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'邮箱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Wsyj_User', @level2type=N'COLUMN',@level2name=N'Mail'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工作' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Wsyj_User', @level2type=N'COLUMN',@level2name=N'DoWork'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工作年限' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Wsyj_User', @level2type=N'COLUMN',@level2name=N'WorkYear'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工作地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Wsyj_User', @level2type=N'COLUMN',@level2name=N'WorkAddress'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'名片地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Wsyj_User', @level2type=N'COLUMN',@level2name=N'Card'
GO

2，编写存储过程

CREATE PROCEDURE  [dbo].[Wsyj_GetUserlist]
   
@name varchar(50)  ,
@pagesize int, --每页数量
@pagenumber int ,--当前第几页
@sort  varchar(50),  --排序字段
@order  varchar(50)  --值 desc/asc 
AS
BEGIN

SET NOCOUNT ON;
	
	DECLARE @condition varchar(1000);		
    set @condition='';  --需要初始化
     
    
	IF @name <> ''	
	BEGIN
	SET @condition += '  AND name like ''%' + @name + '%''';  --查询条件的拼接
	END
	  
	
	DECLARE @PX varchar(1000);  
	set @PX=' '+@sort +'  '+@order+' '   --排序，根据什么字段升序和倒序

    
    DECLARE @num2 int;
	SET  @num2=(@pagenumber-1)*@pagesize;

	set @condition=N' SELECT  TOP '+CAST(@pagesize as varchar(10)) +' *
	FROM  (SELECT ROW_NUMBER() over(ORDER BY '+@PX+') as rowID,*  FROM 
	(   SELECT Uid, Name, Sex, Phone, Mail, DoWork, WorkYear, WorkAddress, Card,Organization  FROM  [dbo].[Wsyj_User]  WHERE  IsDel=0    '+@condition+'  ) AS P  )ss  
	WHERE ss.rowID >'+ cast(@num2 as varchar(10))+'    ';
	

	SET @condition=N'SELECT  COUNT(uid) AS sumnum  FROM [dbo].[Wsyj_User] WHERE  IsDel=0   '+@condition+'  ';  
	 
	  
	 EXEC(@condition) 

END

3，效果

--执行
exec  Wsyj_GetUserlist '111',6,1,'uid','desc'

当然如果是玩sqlserver 的朋友，https://blog.csdn.net/weixin_42780928/article/details/91954951 看我这篇文章！更加轻盈