Problem using Top for Cursor
-
Dear All!
I am having problem in using Top in a Stored Procedure.
I want the: Top <as many as I specify> EmpID as cursor,
Declare curs_Emps cursor for
" select Top <specifedbyme> EmpID from table_Employees where EmpID=@EmpID "
but I am getting errors.
Please help me in this regard.
I'll be really thankful.
-
SQL Server 2000 does not allow you to use a variable in the top statement. SQL Server 1005 changes that. In SQL 2000, to be able to specify a different value for the TOP, you either have to use SET ROWCOUNT before the query with the the value you desire and reset it to 0 after the query; or you need to use dynamic sql and build the query string inside your stored procedure each time it is run.
-
What is the DDL for table_employees and your procedure? I would tend to think that EmpID would be unique for each record in table_Employees. If so, why would you need to use TOP in your query, you will always only get one record. And more importantly, why use a cursor?
-
Post the cursor and some info about what it's supposed to be doing. A little table info would help, too. You'd be amazed at what folks can do about getting rid of a cursor.
Also, the sub for SELECT TOP <specify> is SET ROWCOUNT <specify>... just don't forget to set it back to 0 when you're done.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
ROWCOUNT limits the number of rows a query will use. ROWCOUNT n - n is the number of rows you want to return. 0 is unlimited.
SET ROWCOUNT 1
SELECT * FROM table_Employees
SET ROWCOUNT 0 -- default setting of the connection
is the same as
SELECT TOP 1 * FROM table_Employees
While TOP only affects the query it is included in, ROWCOUNT remains in affect until it is explicitly changed or the connection is closed. With ROWCOUNT, you can use a variable to set the count( SET ROWCOUNT @cnt).
Can you post the entire cursor. I believe we should be able to come up with a set based solution that will perform much faster and will be easier to understand.
Brian
-
Nicely done, Brian. And, Jan, I agree... we've asked you to post the cursor code a couple of times now...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
CREATE PROCEDURE [InsertID]
@CityID varchar(2),
@Top int
AS select CityID from table_Employees where cityid=@CityID
declare @RegionID as varchar(5)
set @RegionID = (select isnull(max(EmpID),0) from table_Employees where cityid=@CityID
if cast(@RegionID as int)=0
set @RegionID = cast(@EmpID as varchar(3)) + cast(@CityID as varchar(2))
Declare @sql varchar(500)
Declare @EmpID int
Declare cur_Emp cursor For
Select Top @Top EmpID From table_Employees where CityID=@CityID
Open cur_Emp
Fetch Next From cur_Emp Into @EmpID
While @@Fetch_Status = 0
Begin
Begin Tran T1
Set @sql = 'update table_Employees set RegionID='+cast(@RegionID as varchar(5))+'where cityid='+cast(@CityID as varchar(2))+' and EmpID='+cast(@EmpID as varchar(3))
exec(@SQL)
Set @RegionID = @RegionID +1
Commit Tran T1
Fetch Next From cur_Emp Into @EmpID
End
-
So you want to set the RegionID value to the maximum empid for a given city?
UPDATE e
SET RegionID=t.EmpID
FROM table_Employees e, (SELECT CityID, MAX(EmpID) EmpID FROM table_Employees GROUP BY CityID) t
WHERE e.CityID=t.CityID
If not, can you provide a description of what you want to accomplish.
Brian
-
That's what I'm talking about
Once we know what you need, Jan, lots of folks have great solutions to avoid the cursor. Why so much emphasis on avoiding the cursor? Because true set based solutions are so much faster and use lot's less resources. Some will tell you that "speed doesn't matter". Most of those folks haven't been up all night baby-sitting slothful code. Besides, you think that maybe the CPU takes breaks? Nope... long running code is long running because it makes the CPU or disk system real busy.Brian's solution looks pretty good to me (not that that matters :hehe
because he hasn't used a cursor or while loop, hasn't used a correlated subquery (hidden RBAR), the join is simple, and there's no triangular join involved. In the face of a decent index or two, his is smokin' code even on large tables.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply