Solved

Select Top (Variable) SQL 2000 vs SQL 2005

Posted on 2007-11-19
4
2,454 Views
Last Modified: 2010-04-21
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
Comment
Question by:Hers2keep
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 20313746
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
 

Author Closing Comment

by:Hers2keep
ID: 31409968
Absolutely perfect!!
0
 

Expert Comment

by:vikkyv
ID: 20352334
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20354895
this sp wont recompile every time,
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now