Using Parameter in a SELECT TOP 2

[globalstrata]

Hello,

I am trying to create a store procedure that display random items using SQL 2005 and the below code but it tells me: Incorrect syntax near '@RecordAmount'. Can any one tell me what am I doing wrong?

CREATE PROCEDURE dbo.sp_GetRandomProduct 
	@CatID int,
	@RecordAmount int

AS

  SELECT  TOP @RecordAmount dbo.Product.ProductID 
  FROM    dbo.Product
  ORDER BY NEWID() 

RETURN

Thanks in advanced,


Gladys Rodriguez
GlobalStrata Solutions
Computer Repair, Website Design and Computer Consultant
Small Business Resources

 

[jbenson001]

TOP will not accept a variable that way. You would have to use dynamic SQL to do that.

Jim

 

[jbenson001]

see this FAQ about dynamic SQL
faq183-3132

 

[johpje]

globalstrata,

TOP is used to return only a part of the total result set.

You have to specify a number. In your case:

CREATE PROCEDURE dbo.sp_GetRandomProduct
    @RecordAmount int [b]out[/B]

AS

SELECT [b]TOP 1 @RecordAmount=dbo.Product.ProductID[/b]
  FROM    dbo.Product
  ORDER BY NEWID()

specify the out keyword for the @RecordAmount parameter to use it after you made the call.

Cheers,
Johan

 

[gmmastros]

It looks like you want to pass a variable in to the stored procedure to determine the number of records to return. If I interpreted this correctly, then you can do this without dynamic sql, like this...

CREATE PROCEDURE dbo.sp_GetRandomProduct 
    @CatID int,
    @RecordAmount int

AS

  [!]Set Rowcount @RecordAmount[/!]

  SELECT  dbo.Product.ProductID 
  FROM    dbo.Product
  ORDER BY NEWID() 

RETURN

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[globalstrata]

ok. It seems that the last two do not work. What I am trying to do is this

1. The table has X number of records
2. I want to display Y random records from the table

My code seems to do some random generation. However, it shows about 10 items no matter how many items I tell it to show. I would like to use a variable in order to define the amount of records to show.

In addition, it only randomize the first 10 records instead of using the X amount in the table.

Thanks in advanced,


Gladys Rodriguez
GlobalStrata Solutions
Computer Repair, Website Design and Computer Consultant
Small Business Resources

 

[globalstrata]

Nevermind. It will help if I used a table with enough records! I used gmmastros since it worked like I needed to. Thanks!!


Gladys Rodriguez
GlobalStrata Solutions
Computer Repair, Website Design and Computer Consultant
Small Business Resources