February 14, 2005 at 12:32 pm
I am writing a report where the user needs to be able to specify how many records they want returned. Is there a good way to do the following:
DECLARE @Top int
SELECT TOP @Top FROM ....
I know that this won't work as it is but is there a way I can get the same effect?
February 14, 2005 at 2:27 pm
I've heard the next release of SQL Server will support dynamic TOP(n) like you've written, but until then, some dynamic SQL should do the job:
DECLARE @top varchar(10)
DECLARE @sql varchar(100)
SET @TOP = 2
SET @sql = 'SELECT TOP ' + @top + ' Field FROM MyTable'
PRINT @sql
EXEC(@sql)
February 15, 2005 at 2:12 am
Consider the following:
declare @rowcount int
set @rowcount = 100
set rowcount @rowcount
select * from MyTable
Bye
Gabor
February 15, 2005 at 2:14 am
Please refrain from posting the same question multiple times here!
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=161542
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 15, 2005 at 7:29 am
I use this, it's not for reports but for data sampling:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE NTH
@table varchar(100),
@destination varchar(100),
@number varchar(100)
AS
DECLARE @sql varchar(2000)
select @sql='select top '+@number+ ' *
into ' +@destination+
' from ' +@table+
' order by NEWID()'
exec(@sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply