Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Question Unanswered: Random Record with TOP parameter

    I'm familiar with querying for a random record, however I can't seem to do it using a parameter driven TOP value.

    Here's what I've tried in a stored procedure:
    (
    @Count int = 1
    )
    declare @sql varchar(1000)
    set @sql = 'SELECT DISTINCT TOP ' + convert(varchar, @Count) + ' * FROM RandomisedView'


    The view I am using looks like:

    SELECT TOP 100 PERCENT dbo.TableName.* ORDER BY NEWID()

    The stored procedure does not seem to cause the View to reorder the records.

    I need to be able to select X random records. Any help would be appriciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Works for me...

    Code:
    USE Northwind
    GO
    
    DECLARE @sql varchar(7000), @x int, @Table_Name sysname, @i int
    
    SELECT @x = 10, @Table_Name = 'Orders', @i = 0
    
    SELECT @sql = 'SELECT TOP ' +  CONVERT(varchar(10),@x) + ' * FROM [' + @Table_Name + '] ORDER BY NewID()'
    
    WHILE @i < 5
      BEGIN
    	EXEC(@sql)
    	SELECT @i = @i + 1
      END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •