CODEWITHSUNDEEP
sql-serverdynamic-sqldatabase-cursor
Jón Trausti
Jón Trausti

Reputation: 157

In SQL Server, is there a way to avoid using a Cursor?

I have a table where each record has a Table_Name (name of a table). I then use a Cursor to select all table names related to some record in to a Cursor. Then I do a WHILE for each table name in the Cursor to do some job.

I want to know if it's possible to solve this problem without using a Cursor.

DECLARE tables_cursor CURSOR FAST_FORWARD FOR SELECT Table_Name FROM Some_Table WHERE ...

FETCH NEXT FROM tables_cursor INTO @Dynamic_Table_Name
WHILE @@FETCH_STATUS = 0
BEGIN
...
END

Foreach table name in the cursor I do a dynamic SQL query like this:

SELECT @sql = '
    UPDATE dbo.' + @Dynamic_Table_Name + '
    SET ...'
EXEC sp_executesql @sql, @params, ...

My question is this: Is it possible to avoid using Cursor to solve this problem?

Unfortunately the design of having table name to reference a table can't be changed, of which I would have done immediately if I could.

Upvotes: 5

Views: 4478

Answers (3)

RameshVel
RameshVel

Reputation: 65867

yes, you can solve this problem without using cursor. Instead you need to introduce the new table which stores the table name from actual table along with auto generated id column.

Check out the below sample query 

declare @test table (id  int identity,tableName varchar(20))

insert into @test 
            select 'abc' union all
            select '123' union all
            select '345' union all
            select 'sdf' union all
            select 'uhyi'

instead above query, you can use your query to populate the table variable

insert into @test 
  SELECT Table_Name FROM Some_Table WHERE ...

And

--select * from @test

declare @cnt int
declare @incr int
select @cnt = count(id) from @test
set @incr = 1
while (@incr <= @cnt)
begin
    select tableName from @test where id = @incr
    set @incr =@incr + 1
end

Upvotes: 2

AakashM
AakashM

Reputation: 63338

Well, you can hide the use of a cursor by using the (undocumented, but widely-used) MS stored procedure sp_MSforeachdb (Google has lots of examples); but that uses a cursor internally, so if it's a philosophical objection then that doesn't really help.

I don't think there can be a set-based way to do this kind of thing, since each table probably has a different relational structure.

Upvotes: 1

Guffa
Guffa

Reputation: 700262

Yes, you could avoid the cursor, but you can't avoid the dynamic queries.

You could possibly make a query that returns all the dynamic queries concatenated together as a single string. That way you could execute them all without using a loop, but that's not really any better...

If you can't change the database design, you are stuck with dynamic queries.

Upvotes: 1

Related Questions