• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2482
  • Last Modified:

Select Top (Variable) SQL 2000 vs SQL 2005

The stored proc below works on an SQL 2005 db, but doesn't work on SQL 2000.
It errors out on the SELECT line 14. I've tried changing the parens to brackets but that didn't work either.
This is the error:

Msg 170, Level 15, State 1, Procedure sp_SelectRandomEmployees, Line 14
Line 14: Incorrect syntax near '('.
USE [HR]
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectRandomEmployees]    
Script Date: 11/19/2007 07:58:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SelectRandomEmployees] 
@RndNum INT
 
AS
BEGIN
 
SET NOCOUNT ON;
 
SELECT TOP (@RndNum) File#, LastName, FirstName, SiteID, Department
FROM Associates 
ORDER By NEWID()
END

Open in new window

0
Carla Romere
Asked:
Carla Romere
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello Hers2keep,

The 'TOP (@Variable) ' wont work in sql 2000


ALTER PROCEDURE [dbo].[sp_SelectRandomEmployees]
@RndNum INT
 
AS
BEGIN
 
SET NOCOUNT ON;
SET ROWCOUNT  @RndNum
 
SELECT File#, LastName, FirstName, SiteID, Department
FROM Associates
ORDER By NEWID()

SET ROWCOUNT 0
END






Aneesh R
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
Absolutely perfect!!
0
 
vikkyvCommented:
Hi,
I have been looking for this solution.  Thanks!  But I am looking at the performance.  I am not sure if the Stored Procedure gets recompiled every time, when we use SET statement.  Could anyone throw some light on this aspect?
Vivek
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
this sp wont recompile every time,
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now