|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
TOP keywordI have stored procedure with @rows int parameter.
Rows means how many rows should procedure returns. How can I say (without dynamic sql): SELECT top @rows * from table regards,S Hi
You can't. Dynamic SQL is required. In SQL Server 2000, SET Rowcount does not support a variable and neither does SELECT TOP. You can always use temp tables and run cursors over them until you have the right count. Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: m***@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "simon" <simon.zu***@iware.si> wrote in message news:0LCUe.174$h6.52339@news.siol.net... >I have stored procedure with @rows int parameter. > Rows means how many rows should procedure returns. > > How can I say (without dynamic sql): > > SELECT top @rows * from table > > regards,S > > In SQL Server 2000, SET Rowcount does not support a variable Mike, the BOL states that ROWCOUNT can accept an int parameter and it seems to work as documented. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message news:%23xiB4xhtFHA.3316@TK2MSFTNGP12.phx.gbl... > Hi > > You can't. Dynamic SQL is required. > > In SQL Server 2000, SET Rowcount does not support a variable and neither > does SELECT TOP. > > You can always use temp tables and run cursors over them until you have > the right count. > > Regards > -------------------------------- > Mike Epprecht, Microsoft SQL Server MVP > Zurich, Switzerland > > IM: m***@epprecht.net > > MVP Program: http://www.microsoft.com/mvp > > Blog: http://www.msmvps.com/epprecht/ > > "simon" <simon.zu***@iware.si> wrote in message > news:0LCUe.174$h6.52339@news.siol.net... >>I have stored procedure with @rows int parameter. >> Rows means how many rows should procedure returns. >> >> How can I say (without dynamic sql): >> >> SELECT top @rows * from table >> >> regards,S >> > > Actually, you can with ANSI-SQL:
select m1.Col1 , m2.Col2 from MyTable m1 join MyTable m2 on m2.Col1<= m1.Col1 group by m1.Col1 , m2.Col2 having count (*) < @rows Assumes PK is Col1. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message You can't. Dynamic SQL is required.news:%23xiB4xhtFHA.3316@TK2MSFTNGP12.phx.gbl... Hi In SQL Server 2000, SET Rowcount does not support a variable and neither does SELECT TOP. You can always use temp tables and run cursors over them until you have the right count. Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: m***@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "simon" <simon.zu***@iware.si> wrote in message news:0LCUe.174$h6.52339@news.siol.net... >I have stored procedure with @rows int parameter. > Rows means how many rows should procedure returns. > > How can I say (without dynamic sql): > > SELECT top @rows * from table > > regards,S > Hi,
CREATE PROC proc1 @rows int AS Begin SET ROWCOUNT @rows SELECT * from t1 Set rowcount 0 EN D The above will work with out dynamic SQL. IN the the case of TOP you have to use Dynamic SQL. Thanks Hari SQL Server MVP <markc***@hotmail.com> wrote in message Show quote news:1126365597.541439.105790@g43g2000cwa.googlegroups.com... > SET ROWCOUNT @rows > SELECT * from table > Others have already suggested SET ROWCOUNT. However, this doesn't make much
sense unless you have an ORDER BY clause because you can't reliably determine which rows you will get back. Furthermore, even with an ORDER BY clause the results may not be entirely predictable unless the ORDER BY list includes key columns that are unique within the table. It's usually important for code to give deterministic results so make sure you specify the right sort order. -- David Portas SQL Server MVP --  |
|||||||||||||||||||||||
Other interesting topics