sql declare cursor with variable error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql declare cursor with variable error

Author

Topic

lukit
Starting Member

6 Posts

Posted - 2005-08-26 : 10:39:55

DECLARE Total SCROLL CURSOR FOR
SELECT count(Model) FROM @lcTableListBeforeLast where Store=@lcLocationName

The error is: Must declare the variable '@lcTableListBeforeLast'.
I`ve declared it before.

When I use something like this

EXECUTE('DECLARE Total SCROLL CURSOR FOR SELECT count(Model) FROM ['+@lcTableListBeforeLast+'] where Store='+@lcLocationName)

I got error: A cursor with the name 'Total' does not exist.

Please help with this..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 10:49:08

You cannot use Variable in place of tableName unless you use Dynamic SQL
Can you explain on what you are trying to do?
Why do you use cursor?

Madhivanan

Failing to plan is Planning to fail

lukit
Starting Member

6 Posts

Posted - 2005-08-26 : 11:11:57

BEGIN
SET NOCOUNT ON

DECLARE LocationName SCROLL CURSOR FOR
SELECT Name,Date FROM comp_base

DECLARE @lcLocationName VARCHAR(100)
DECLARE @lcTotal BIGINT
DECLARE @lcTransOld VARCHAR(100)
DECLARE @lcOldTotal BIGINT
DECLARE @lcDate SmallDateTime

OPEN LocationName
FETCH NEXT FROM LocationName INTO @lcLocationName, @lcDate

WHILE (@@FETCH_STATUS = 0)

BEGIN

-- 000000000000000000000000000000000000000000000000000000

DECLARE Total SCROLL CURSOR FOR
SELECT count(Model) FROM XXXXXXX <----here I want to put variable
where Store=@lcLocationName

print @lcLocationName

OPEN Total
FETCH NEXT FROM Total INTO @lcTotal

-- UPDATE a SET Total=@lcTotal FROM [comp_base] a where Name=@lcLocationName

print @lcTotal

CLOSE Total
DEALLOCATE Total

-- 000000000000000000000000000000000000000000000000000000

DECLARE OldTotal SCROLL CURSOR FOR

select Total,TransferedTo from carmax_base where Name=@lcLocationName

-- print @lcLocationName

OPEN OldTotal
FETCH NEXT FROM OldTotal INTO @lcOldTotal,@lcTransOld

-- UPDATE a SET TransferedTo=@lcTransferedTo,TotalTransferedTo=@lcTransferedToTotal FROM [carmax_base] a where Name=@lcLocationName

-- print @lcTransferedTo
-- print @lcTransferedToTotal
print '-------'
print @lcOldTotal

-- set @lcTransferedToTotal = 0
-- set @lcTransferedTo = ' '

CLOSE OldTotal
DEALLOCATE OldTotal

-- 000000000000000000000000000000000000000000000000000000

IF @lcOldTotal > 0 AND @lcTotal = 0
BEGIN
print 'HEEEEEEEEEEEEEEEEEEEEEEEEEEEEJJJJJJJJJJJJJJJJJJJJJJJJJJJJ'
print @lcTransOld
print '----------------'
UPDATE a SET Date=@lcDate FROM [carmax_base] a where Name=@lcTransOld

END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 00:34:03

The General way of paasing table name is

Declare @table varchar(30)
set @table='yourTable'
Exec('Select Columns from '+@table)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources