增删改查分页存储过程

--创建数据库
CREATE DATA  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