string.Format("if @MaxItems>0 begin select top {0}* From books end else begin select * From books end",@MaxItems);
建立一个存储过程, 只要调用的时候指定参数就可以了:CREATE PROCEDURE [dbo].[SelectMax] @MaxItems int=0 /*指定默认值*/ ASif (@MaxItems = 0) Select * From News else begin set rowcount @MaxItems Select * From News endGO
DECLARE @MaxItems INT SET @MaxItems=0 --这里只是随便给个值,实际上这个值不可预知 SELECT @MaxItems= ISNULL(NULLIF(@MaxItems,0),10000000)EXEC('SELECT TOP ' + @MaxItems + ' * FROM books') 或者直接 SET ROWCOUNT ISNULL(@MaxItems,0) SELECT * FROM books SET ROWCOUNT 0
上面第二种写法错了. SET ROWCOUNT中不能用函数,写忘了.SELECT @MaxItems=ISNULL(@MaxItems,0) SET ROWCOUNT @MaxItems SELECT * FROM books SET ROWCOUNT 0
但可变通string sCount = "";
int iCount = 0;
****if(iCount > 0)
sCount = " top "+ iCount.ToString();strSQL = "Select "+ sCount +" * FROM books"
Select * From books
else
Select top(@MaxItems) * From books
*****************************************************************************
欢迎使用CSDN论坛阅读器 : CSDN Reader(附全部源代码)
http://www.cnblogs.com/feiyun0112/archive/2006/09/20/509783.html
@MaxItems int=0 /*指定默认值*/
ASif (@MaxItems = 0)
Select * From News
else
begin
set rowcount @MaxItems
Select * From News
endGO
SET @MaxItems=0 --这里只是随便给个值,实际上这个值不可预知
SELECT @MaxItems= ISNULL(NULLIF(@MaxItems,0),10000000)EXEC('SELECT TOP ' + @MaxItems + ' * FROM books')
或者直接
SET ROWCOUNT ISNULL(@MaxItems,0)
SELECT * FROM books
SET ROWCOUNT 0
SET ROWCOUNT中不能用函数,写忘了.SELECT @MaxItems=ISNULL(@MaxItems,0)
SET ROWCOUNT @MaxItems
SELECT * FROM books
SET ROWCOUNT 0