Cursor Error...

...I can't seem to work it out, maybe you can help. I get a
Server: Msg 137, Level 15, State 2, Line 22
Must declare the variable '@.tbl_name'.
when I run...
declare @.counter int, @.tbl_name varchar(40)
select @.counter = 1
declare loop_cursor cursor for SELECT distinct name FROM sysobjects(nolock)
WHERE NAME in (
'marc', 'marc1') and xtype = 'U'
open loop_cursor
fetch loop_cursor into @.tbl_name
WHILE @.@.fetch_status = 0
BEGIN
UPDATE @.tbl_name
SET marcA = substring(marcA,1,2)
WHERE marc = 1
PRINT @.counter
PRINT '____________________________'
PRINT @.tbl_name + 'Post Codes Updated'
PRINT ' '
SELECT @.counter = @.counter + 1
END
FETCH loop_cursor INTO @.tbl_name
CLOSE loop_cursor
DEALLOCATE loop_cursor
am i missing something blatently obvious, it's friday so probably so arrrgh
The creates are as follows for anyone wishing to try it
drop table marc, marc1
create table marc (marc int, marcA varchar(5))
create table marc1 (marc int, marcA varchar(5))
insert into marc values(1, 'ABC')
insert into marc values(2, 'ABC')
insert into marc1 values(1, 'ABC')
insert into marc1 values(2, 'ABC')The SQL Server parser doesn't resolve variables as table/column names.
You'll need to use dynamic SQL to do this in Transact-SQL.
See http://www.sommarskog.se/dynamic_sql.html for an article on dynamic SQL
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:38C354EE-43DC-42D8-B4B9-E503B72D34EA@.microsoft.com...
> ...I can't seem to work it out, maybe you can help. I get a
> Server: Msg 137, Level 15, State 2, Line 22
> Must declare the variable '@.tbl_name'.
> when I run...
> declare @.counter int, @.tbl_name varchar(40)
> select @.counter = 1
> declare loop_cursor cursor for SELECT distinct name FROM
> sysobjects(nolock)
> WHERE NAME in (
> 'marc', 'marc1') and xtype = 'U'
> open loop_cursor
> fetch loop_cursor into @.tbl_name
> WHILE @.@.fetch_status = 0
> BEGIN
> UPDATE @.tbl_name
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1
> PRINT @.counter
> PRINT '____________________________'
> PRINT @.tbl_name + 'Post Codes Updated'
> PRINT ' '
> SELECT @.counter = @.counter + 1
> END
> FETCH loop_cursor INTO @.tbl_name
> CLOSE loop_cursor
> DEALLOCATE loop_cursor
>
> am i missing something blatently obvious, it's friday so probably so
> arrrgh
>
> The creates are as follows for anyone wishing to try it
> drop table marc, marc1
> create table marc (marc int, marcA varchar(5))
> create table marc1 (marc int, marcA varchar(5))
> insert into marc values(1, 'ABC')
> insert into marc values(2, 'ABC')
> insert into marc1 values(1, 'ABC')
> insert into marc1 values(2, 'ABC')|||thanks Dan, I replaced
/*
UPDATE @.tbl_name1
SET marcA = substring(marcA,1,2)
WHERE marc = 1
*/
to dynamic as follows but it only updates the first row of the first
table...Any ideas
Set @.Command = ' UPDATE ' + @.tbl_name + '
SET marcA = substring(marcA,1,2)
WHERE marc = 1 '
Execute sp_executesql @.Command|||> but it only updates the first row of the first
> table...Any ideas
It appears you need a FETCH within the WHILE loop. Try:
declare @.counter int, @.tbl_name varchar(40)
declare @.command nvarchar(4000)
select @.counter = 1
declare loop_cursor cursor for SELECT name FROM sysobjects(nolock)
WHERE NAME in (
'marc', 'marc1') and xtype = 'U'
open loop_cursor
fetch loop_cursor into @.tbl_name
WHILE @.@.fetch_status = 0
BEGIN
Set @.Command = ' UPDATE ' + @.tbl_name + '
SET marcA = substring(marcA,1,2)
WHERE marc = 1 '
Execute sp_executesql @.Command
PRINT @.counter
PRINT '____________________________'
PRINT @.tbl_name + 'Post Codes Updated'
PRINT ' '
SELECT @.counter = @.counter + 1
FETCH loop_cursor INTO @.tbl_name
END
CLOSE loop_cursor
DEALLOCATE loop_cursor
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E06D4572-A6BC-463C-A6E0-17FE3F68E802@.microsoft.com...
> thanks Dan, I replaced
> /*
> UPDATE @.tbl_name1
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1
> */
> to dynamic as follows but it only updates the first row of the first
> table...Any ideas
> Set @.Command = ' UPDATE ' + @.tbl_name + '
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1 '
> Execute sp_executesql @.Command
>|||thankyou Dan, appreciate it.
"Dan Guzman" wrote:

> It appears you need a FETCH within the WHILE loop. Try:
>
> declare @.counter int, @.tbl_name varchar(40)
> declare @.command nvarchar(4000)
> select @.counter = 1
> declare loop_cursor cursor for SELECT name FROM sysobjects(nolock)
> WHERE NAME in (
> 'marc', 'marc1') and xtype = 'U'
> open loop_cursor
> fetch loop_cursor into @.tbl_name
> WHILE @.@.fetch_status = 0
> BEGIN
> Set @.Command = ' UPDATE ' + @.tbl_name + '
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1 '
> Execute sp_executesql @.Command
> PRINT @.counter
> PRINT '____________________________'
> PRINT @.tbl_name + 'Post Codes Updated'
> PRINT ' '
> SELECT @.counter = @.counter + 1
> FETCH loop_cursor INTO @.tbl_name
> END
> CLOSE loop_cursor
> DEALLOCATE loop_cursor
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:E06D4572-A6BC-463C-A6E0-17FE3F68E802@.microsoft.com...
>
>sql