Dynamic cursor used in a block in TSQL?

Question

I have the following TSQL codes:

-- 1. define a cursor
DECLARE c_Temp CURSOR FOR
    SELECT name FROM employees;

DECLARE @name varchar(100);
-- 2. open it
OPEN c_Temp;
-- 3. first fetch
FETCH NEXT FROM c_Temp INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
  print @name;
  FETCH NEXT FROM c_Temp INTO @name; -- fetch again in a loop
END
-- 4. close it
....

I use the name value only in a loop block. Here I have to 1)define a cursor variable, 2)open it, 3)fetch twice and 4)close it. In PL/SQL, the loop can be like this:

FOR rRec IN (SELECT name FROM employees) LOOP
  DBMS_OUTPUT.put_line(rRec.name);
END LOOP;

It is much simpler than my TSQL codes. No need to define a cursor. It is created dynamically which is accessible within a loop block (much like C# for loop). Not sure if there something similar like this in TSQL?

Answer 1

Something along these lines might work for you, although it depends on having an ID column or some other unique identifier

Declare @au_id Varchar(20)
Select @au_id = Min(au_id) from authors

While @au_id IS NOT NULL
Begin
            Select au_id, au_lname, au_fname from authors Where au_id = @au_id
            Select @au_id = min(au_id) from authors where au_id > @au_id
End

Answer 2

Since you are coming from an Oracle background where cursors are used frequently, you may not be aware that in SQl Server cursors are performance killers. Depending on what you are actually doing (surely not just printing the variable), there may be a much faster set-based solution.

Answer 3

Cursors are evil in Sql Server as they can really degrade performance - my favoured approach is to use a Table Variable (>= Sql Server 2005) with an auto inc ID column:

Declare @LoopTable as table (
     ID int identity(1,1),
     column1 varchar(10),
     column2 datetime
)
insert into @LoopTable (column1, column2)
select name, startdate from employees

declare @count int
declare @max int
select @max = max(ID) from @LoopTable 
select @count = 1

while @count <= @max
begin
   --do something here using row number '@count' from @looptable
   set @count = @count + 1
end

It looks pretty long winded however works in any situation and should be far more lightweight than a cursor

Answer 4

In some cases, its also possible to use trick like this one:

DECLARE @name VARCHAR(MAX)

SELECT  @name = ISNULL(@name + CHAR(13) + CHAR(10), '') + name
FROM    employees

PRINT   @name

For a list of employee names.

It can also be used to make comma-separated string, just replace + CHAR(13) + CHAR(10) with + ', '

Answer 5

Why not simply just return the recordset using a select statement. I assume the object is to copy and paste the values in the UI (based on the fact that you are simply printing the output)? In Management studio you can copy and paste from the grid, or press +T and then run the query and return the results as part of the messages tab in plain text.

If you were to run this via an application, the application wouldn't be able to access the printed statements as they are not being returned within a recordset.