存储过程动态显示top条数

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,2011-05-17,12:51>
-- Description:    <Description,,查询最新新闻>
-- =============================================
ALTER PROCEDURE [dbo].[procLrbNewSelect]
@lmbh int ,
@num  int 
AS 
 DECLARE @sql AS varchar(200)
 SELECT @sql=' SELECT  top  '+ cast(@num AS varchar)+' *FROM  lrb  WHERE   lmbh='+ cast(@lmbh AS varchar) +'ORDER BY orderID,lrdt   desc'
exec(@sql)
--执行--

 exec [procLrbNewSelect] 336,2

 

　　

CREATE PROC newsList
@startIndex INT,
@pageSize INT
AS
WITH Temp AS (
	SELECT ROW_NUMBER() OVER( ORDER BY lrdt) AS rownumber,lrbt,l.lrdt
	FROM lrb l)
	SELECT rownumber,lrbt,l.lrdt FROM SClass l
where rownumber >=(@startindex-1)*@pagesize+1 and rownumber<=(@startindex-1)*@pagesize+(@pagesize)

SELECT COUNT(*) FROM lrb l

　　

 

.net调用存储过程

 DataTable dt = new DataTable();
        SqlParameter[] prar ={ new SqlParameter("@lmbh",336),
                            new  SqlParameter("@num",2)};
        new SQLHelper().ExecuteProc("procLrbNewSelect",prar,out dt);