"select TOP" with parameter...

Hi there.
Does anyone know of a way to get the following statement to work without
using dynamic SQL?.....
I'm trying to get a "select TOP" to work while passing in a parameter as 'N'
for 'select the first N rows...'
declare @.NumberofRecords integer
select @.NumberofRecords = 5
select top @.NumberofRecords
from MyTable
Any info would be appreciated!len
Lookup SET ROWCOUNT in the BOL
"len" <len@.discussions.microsoft.com> wrote in message
news:3643D907-3483-4B3D-9F9A-B7D45EABF6AB@.microsoft.com...
> Hi there.
> Does anyone know of a way to get the following statement to work without
> using dynamic SQL?.....
> I'm trying to get a "select TOP" to work while passing in a parameter as
> 'N'
> for 'select the first N rows...'
> declare @.NumberofRecords integer
> select @.NumberofRecords = 5
> select top @.NumberofRecords
> from MyTable
> Any info would be appreciated!|||You are not selecting anything so maybe this will work
select top @.NumberofRecords myField1, myField2, etc.
from MyTable
if that still fails try
EXEC('select top' + @.NumberofRecords + ' myField, myField2, etc. from
MyTable')
HTH,
Gerard|||Len,
you can't use a parameter with TOP in SQL 2000. In 2005 it is possible
see this article for more details:
http://support.microsoft.com/defaul...kb;en-us;891605
In 2000 and 7.0 you can use the SET ROWCOUNT option to achieve what you
want.
Try something like
Declare @.num int
SET @.num = 8
SET ROWCOUNT @.num
select * from myTable
Order by columnname
Markus|||I just did a quick check in Qry Analyzer and
EXEC('select top ' + @.NumberofRecords + ' myField, myField2, etc. from
MyTable')
works, provided offcourse that @.NumberofRecords is a string
Gerard|||"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1130325402.186088.177800@.g14g2000cwa.googlegroups.com...
>I just did a quick check in Qry Analyzer and
> EXEC('select top ' + @.NumberofRecords + ' myField, myField2, etc. from
> MyTable')
> works, provided offcourse that @.NumberofRecords is a string
It does work, but that's not a parameterised procedure, it's dynamic SQL.
Plenty of reasons to try and avoid using that if possible.
Dan|||> select top @.NumberofRecords myField1, myField2, etc.
> from MyTable
This will fail because TOP does not take parameters in SQL Server 2000!

> EXEC('select top' + @.NumberofRecords + ' myField, myField2, etc. from
> MyTable')
This will fail because (a) @.NumberOfRecords can't implicitly be concatenated
into a string and (b) you didn't leave a space after top, so it will come
out with a syntax error like this:
select top5 myColumn, myColumn2, ...
Please see http://www.sommarskog.se/dynamic_sql.html to understand why
dynamic SQL is not always the best knee-jerk reaction to a problem...|||Yes yes yes,
I know about the limitations/dangers/restrictions with/of dynamic sql,
I was a bit quick of the gun maybe.
And thanks for checking on the punctuation, e.g. the space after the
top.
On your point b)

> works, provided offcourse that @.NumberofRecords is a string
which would, I forgot to mention, require an additional variable and a
CONVERT if the initial variable is an int.

"Dynamic" sorting inside a procedure

Hello,
I need to have a stored procedure, which performs sorting. Something like
this:

CREATE PROCEDURE procname
@.sortby varchar(30)
AS
BEGIN
SELECT some, columns
FROM some_table
ORDER BY @.sortby
END

(of course, i know this won't work, but it gives the idea of what i mean)
Is there a possibility to write a procedure which behaves like that? It is
important for me not to have multiple procedures just for different sorting
criteria...

Thanks,
MikeOne method is to build and execute a dynamic SQL statement. For example:

EXEC
(
'SELECT some, columns
FROM some_table
ORDER BY ' + @.sortby
)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Michal Grabowski" <mgrabow1@.elka.pw.edu.pl> wrote in message
news:cf5h33$g1j$1@.julia.coi.pw.edu.pl...
> Hello,
> I need to have a stored procedure, which performs sorting. Something like
> this:
> CREATE PROCEDURE procname
> @.sortby varchar(30)
> AS
> BEGIN
> SELECT some, columns
> FROM some_table
> ORDER BY @.sortby
> END
> (of course, i know this won't work, but it gives the idea of what i mean)
> Is there a possibility to write a procedure which behaves like that? It is
> important for me not to have multiple procedures just for different
sorting
> criteria...
> Thanks,
> Mike|||Michal Grabowski (mgrabow1@.elka.pw.edu.pl) writes:
> CREATE PROCEDURE procname
> @.sortby varchar(30)
> AS
> BEGIN
> SELECT some, columns
> FROM some_table
> ORDER BY @.sortby
> END
> (of course, i know this won't work, but it gives the idea of what i
> mean) Is there a possibility to write a procedure which behaves like
> that? It is important for me not to have multiple procedures just for
> different sorting criteria...

http://www.sommarskog.se/dynamic_sql.html gives some suggestions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for all tips!

Regards,
Mike

"dynamic" partitioning in SQL 2005 - possibility/speed?

Hi.
We have a workflow system that has tasks (tickets, whatever).
As of today, we have several hundred thousand closed tasks and about
5-7k open tasks.
The majority of operations work on open tasks, and the majority of
reports are also for open tasks; open tasks eventually become closed,
closed tasks can become open too but that's rare.
Is it possible to partition the tasks' table by the IsClosed bit
field, and have SQL server move rows back and forth automatically?
Will it be slow?
Also, for the reports that aggregate data from both small active
partition and big "closed" partition, will there be a big performance
hit compared to a single, non-partitioned table?
Articles on partitioning I have googled are kind of one sided and
mostly deal with static partitions of archived sales data etc that are
maintained manually or using scripts on regular basis.Hmm.. any ideas?

"dynamic" partitioning in SQL 2005 - possibility/speed?

Hi.
We have a workflow system that has tasks (tickets, whatever).
As of today, we have several hundred thousand closed tasks and about
5-7k open tasks.
The majority of operations work on open tasks, and the majority of
reports are also for open tasks; open tasks eventually become closed,
closed tasks can become open too but that's rare.
Is it possible to partition the tasks' table by the IsClosed bit
field, and have SQL server move rows back and forth automatically?
Will it be slow? :)
Also, for the reports that aggregate data from both small active
partition and big "closed" partition, will there be a big performance
hit compared to a single, non-partitioned table?
Articles on partitioning I have googled are kind of one sided and
mostly deal with static partitions of archived sales data etc that are
maintained manually or using scripts on regular basis.Hmm.. any ideas?

"dynamic" partitioning in SQL 2005 - possibility/speed?

Hi.
We have a workflow system that has tasks (tickets, whatever).
As of today, we have several hundred thousand closed tasks and about
5-7k open tasks.
The majority of operations work on open tasks, and the majority of
reports are also for open tasks; open tasks eventually become closed,
closed tasks can become open too but that's rare.
Is it possible to partition the tasks' table by the IsClosed bit
field, and have SQL server move rows back and forth automatically?
Will it be slow?
Also, for the reports that aggregate data from both small active
partition and big "closed" partition, will there be a big performance
hit compared to a single, non-partitioned table?
Articles on partitioning I have googled are kind of one sided and
mostly deal with static partitions of archived sales data etc that are
maintained manually or using scripts on regular basis.
Hmm.. any ideas?