SQL存储过程

[Lewis93]

各位大神，这存储过程怎么写：

[专注or全面]

--前提是你要懂得分页，其他的也没啥，就是sql语句了
--你的表自己建，我这里是个实例，一切照搬就行了，
create table t1
(
	id int,
	Name varchar(50)
)



Create PROCEDURE uspGetBillDIspenses
	-- Add the parameters for the stored procedure here
	@receivebillPerson varchar(50),
	@billType varchar(50),
	@pageIndex int,
	@pageSize int,
	@recordCount int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    select t.* from
    (
		select ROW_NUMBER() over (order by id) as num,* from t1 
			where  Name = @receivebillPerson /* 其他查询条件照搬 */
	) t where num>(@pageIndex -1)*@pageSize and num<=@pageIndex*@pageSize 
	
	select @recordCount = COUNT(1)  from t1 where  Name = @receivebillPerson  /* 其他查询条件照搬 */
	
END
GO

[以学习为目的]

CREATE PROCEDURE uspGetBillDispenses
    @receivebillPerson VARCHAR(50),
    @billType VARCHAR(50),
    @pageIndex INT,
    @pageSize INT,
    @recordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
 
SELECT tb1.* FROM  
(SELECT  ROW_NUMBER() OVER  (ORDER BY PKID) AS id,* FROM BillMgt_BillDispenses
WHERE ReceiverBillPerson LIKE '%'+@receivebillPerson+'%' AND BillType=isnull(@billtype,'')
)tb1 
WHERE  id BETWEEN (@pageIndex-1)*@pageSize+1 AND @pageIndex*@pageSize

SELECT  @recordCount=COUNT(1) FROM BillMgt_BillDispenses
WHERE ReceiverBillPerson LIKE '%'+@receivebillPerson+'%' AND BillType=isnull(@billtype,'')
END
GO
--调用
DECLARE @out INT
EXEC uspGetBillDispenses '@receivebillPerson值','@billType值','@pageIndex值','@pageSize值'，@out OUTPUT
SELECT @out
GO

[专注or全面]

引用 3 楼 u012786375 的回复:

[quote=引用 1 楼 x_wy46 的回复:]

--前提是你要懂得分页，其他的也没啥，就是sql语句了
--你的表自己建，我这里是个实例，一切照搬就行了，
create table t1
(
	id int,
	Name varchar(50)
)



Create PROCEDURE uspGetBillDIspenses
	-- Add the parameters for the stored procedure here
	@receivebillPerson varchar(50),
	@billType varchar(50),
	@pageIndex int,
	@pageSize int,
	@recordCount int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    select t.* from
    (
		select ROW_NUMBER() over (order by id) as num,* from t1 
			where  Name = @receivebillPerson /* 其他查询条件照搬 */
	) t where num>(@pageIndex -1)*@pageSize and num<=@pageIndex*@pageSize 
	
	select @recordCount = COUNT(1)  from t1 where  Name = @receivebillPerson  /* 其他查询条件照搬 */
	
END
GO

如果不加其它查询条件就可以查询到结果，但加了之后就查不到结果 在/* 其他查询条件照搬 */后加上了 + @SQLRC 前面是声明了@SQLRC if(@SQLRC IS NOT NULL) BEGIN SET @SQLRC=' AND BillType='+@billType END[/quote] 那你要看看加的条件正确不，或者指定的条件下有没有值， 这个事sql最基础的了的吧，建议你先把sql基础弄熟练起来，存储过程根本就不是个事 我刚学sql那会，觉得存储过程太复杂了，太神秘了，简直写不出来……，哈哈 后来仔细看看，只不过是有约定的格式，内容里面不过是一大堆sql和参数而已

[Lewis93]

引用 1 楼 x_wy46 的回复:

--前提是你要懂得分页，其他的也没啥，就是sql语句了
--你的表自己建，我这里是个实例，一切照搬就行了，
create table t1
(
	id int,
	Name varchar(50)
)



Create PROCEDURE uspGetBillDIspenses
	-- Add the parameters for the stored procedure here
	@receivebillPerson varchar(50),
	@billType varchar(50),
	@pageIndex int,
	@pageSize int,
	@recordCount int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    select t.* from
    (
		select ROW_NUMBER() over (order by id) as num,* from t1 
			where  Name = @receivebillPerson /* 其他查询条件照搬 */
	) t where num>(@pageIndex -1)*@pageSize and num<=@pageIndex*@pageSize 
	
	select @recordCount = COUNT(1)  from t1 where  Name = @receivebillPerson  /* 其他查询条件照搬 */
	
END
GO

如果不加其它查询条件就可以查询到结果，但加了之后就查不到结果 在/* 其他查询条件照搬 */后加上了 + @SQLRC 前面是声明了@SQLRC if(@SQLRC IS NOT NULL) BEGIN SET @SQLRC=' AND BillType='+@billType END

[Lewis93]

引用 2 楼 galenkeny 的回复:

CREATE PROCEDURE uspGetBillDispenses
    @receivebillPerson VARCHAR(50),
    @billType VARCHAR(50),
    @pageIndex INT,
    @pageSize INT,
    @recordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
 
SELECT tb1.* FROM  
(SELECT  ROW_NUMBER() OVER  (ORDER BY PKID) AS id,* FROM BillMgt_BillDispenses
WHERE ReceiverBillPerson LIKE '%'+@receivebillPerson+'%' AND BillType=isnull(@billtype,'')
)tb1 
WHERE  id BETWEEN (@pageIndex-1)*@pageSize+1 AND @pageIndex*@pageSize

SELECT  @recordCount=COUNT(1) FROM BillMgt_BillDispenses
WHERE ReceiverBillPerson LIKE '%'+@receivebillPerson+'%' AND BillType=isnull(@billtype,'')
END
GO
--调用
DECLARE @out INT
EXEC uspGetBillDispenses '@receivebillPerson值','@billType值','@pageIndex值','@pageSize值'，@out OUTPUT
SELECT @out
GO

有个小问题，（AND BillType=isnull(@billtype,'')）如果BillType是string.Empty的话就查不到数据了