增删改查分页存储过程

--创建数据库
CREATE DATABASE PROCDB
GO
--使用数据库
GO
USE PROCDB
GO
--创建表供存储过程的实现
CREATE TABLE STUDENTINFOS
(
	ID INT PRIMARY KEY IDENTITY,
	S_NAME NVARCHAR(50),
	S_AGE INT,
	S_SEX CHAR(4)
)
GO
--创建存储过程的增删改
CREATE PROC P_INSERTDELETEUPDATE
(
	@ID INT,
	@S_NAME NVARCHAR(50),
	@S_AGE INT,
	@S_SEX CHAR(4)
)
AS
BEGIN
	IF((SELECT COUNT(ID) FROM STUDENTINFOS WHERE ID = @ID) = 0)
	BEGIN
		INSERT INTO STUDENTINFOS VALUES(@S_NAME,@S_AGE,@S_SEX)
	END
	ELSE
		IF(@S_NAME != '' OR @S_AGE != '' OR @S_SEX != '')
		BEGIN
			UPDATE STUDENTINFOS SET S_NAME = @S_NAME,S_AGE = @S_AGE, S_SEX = @S_SEX WHERE ID = @ID
		END
		ELSE	
			DELETE FROM STUDENTINFOS WHERE ID = @ID
END




--存储构成的分页查询
CREATE PROC P_PAGESHOW
(
	@S_NAME NVARCHAR(50),
	@S_AGE INT,
	@S_SEX CHAR(4),
	@PAGEINDEX INT,
	@PAGESIZE INT,
	@PAGECOUNT INT OUTPUT
)
AS
BEGIN
	DECLARE @STR NVARCHAR(MAX)
	DECLARE @STRWHERE NVARCHAR(MAX)
	DECLARE @STRCOUNT NVARCHAR(MAX)
	SET @STR = 'SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID) AS NUM , * FROM STUDENTINFOS WHERE 1=1 '
	SET @STRCOUNT = 'SELECT @PATECOUNT = COUNT(ID) FROM STUDENTINFOS WHERE 1=1 '
	IF(@S_NAME != '')
	BEGIN
		SET @STR +=  ' AND S_NAME LIKE ''%'+@S_NAME+'%'' '
		SET @STRCOUNT +=  ' AND S_NAME LIKE ''%'+@S_NAME+'%'' '
	END
	IF(@S_AGE != '')
	BEGIN
		SET @STR +=  ' AND S_AGE = '''+@S_AGE+''' '
		SET @STRCOUNT +=  ' AND S_NAME LIKE ''%'+@S_NAME+'%'' '
	END
	IF(@S_SEX != '')
	BEGIN
		SET @STR +=  ' AND S_SEX = '''+@S_SEX+''' '
		SET @STRCOUNT +=  ' AND S_NAME LIKE ''%'+@S_NAME+'%'' '
	END
	SET @STRWHERE = ') AS TEMP WHERE TEMP.NUM BETWEEN ('+((@PAGEINDEX-1)*@PAGESIZE)+') AND ('+@PAGESIZE+')'
	SET @STR +=@STRWHERE
	EXEC(@STR)
	EXEC SP_EXECUTESQL @STRCOUNT,  N'@PATECOUNT INT OUTPUT ' , @PAGECOUNT OUTPUT
END