Hi all,

I have been trying to get this to work but keep getting an error. Can
you use a variable table name in a cursor declaration? Like so:

declare mysub cursor for select SUBJID from @SubTable

If not how can I get around this problem???

No, you would have to place the entire processing in dynamic SQL.

Can you explain exactly what you are trying to accomplish?  Why is the table
name variable?  Why do you need a cursor?  What are you really trying to do?

A

Aaron Bertrand [SQL Server MVP] wrote:
I am trying to merge 12 tables into one. All 12 tabls have 6 columns in
common. so I am trying to compare so I can determine to do a update or
an insert...

I think the Dynamic SQL will work....

Thanks,
JC

Yes, with dynamic SQL:

DECLARE @sql varchar(...)
SET @sql = 'declare mysub cursor for select SUBJID from ' + @SubTable
EXEC @sql
OPEN ...

However, not knowing the table name in advance ten to indicate a weak data model. Also, dynamic SQL
has lots of drawbacks, see the articles at www.sommarskog.se. So has usage of cursors in general,
compared to set based SQL.

Show quoteHide quote

joseph.chewn***@targacept.com wrote:
Joseph,

You are SELECT statement is invalid.  You cannot select from a
variable, that doesn't make sense.  Unless you trying to create dynamic
SQL as Aaron & Tibor mention.

You can create a variable and assign it to a cursor (see example below)
but there is no point as the variable is static (unless inside a WHILE
loop).
I really would recommend that you use cursor's as your last possible
resort.  Try to see if you can tackle your issue without cursors (most
of the time you can).

DECLARE @Today datetime
DECLARE @ADate datetime

SET @Today = (SELECT GETDATE())

DECLARE curCursor CURSOR FOR SELECT @Today

OPEN curCursor

FETCH NEXT FROM curCursor INTO @ADate

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @ADate

    FETCH NEXT FROM curCursor INTO @ADate

END

CLOSE curCursor
DEALLOCATE curCursor
GO

Regards,

    -Tav.-

Tavis Pitt

Hi Joseph

This will allow you to do what you are attempting:

please note I used the Northwind  db for this example

USE Northwind

if object_id('tempdb..#Bajo') is not null
    drop table #Bajo

Create Table #Bajo (Nempid int null )

declare @SubTable varchar(50)
declare @NewID  int



Set @SubTable = 'Employees'

exec ('Insert #Bajo select EmployeeID from '+ @SubTable )

declare mysub cursor
    for select * from #Bajo

open mysub
    fetch next from mysub into @Newid
    while @@fetch_Status = 0
    begin
        Select @NewID 'EmployeeID'
        fetch next from mysub into @Newid

    end

close mysub
deallocate mysub



All the best
Baj-SGC818


joseph.chewn***@targacept.com wrote:
Show quoteHide quote

random select a row based a weight column.
Timeout expire
Optimize this query
store results over network
How can I insert a new field between existing field?