Solved

Select Top (Variable) SQL 2000 vs SQL 2005

Posted on 2007-11-19
4
2,456 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

813 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

17 Experts available now in Live!

Get 1:1 Help Now