|
Home > Archive > MS SQL Server security > April 2005 > Cursor Error...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| marcmc 2005-04-01, 8:01 pm |
| ....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')
| |
| Dan Guzman 2005-04-01, 8:01 pm |
| 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@microso
ft.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')
| |
| marcmc 2005-04-01, 8:01 pm |
| 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
| |
| Dan Guzman 2005-04-01, 8:01 pm |
| > 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@microso
ft.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
>
>
| |
| marcmc 2005-04-01, 8:01 pm |
| 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@microso
ft.com...
>
>
>
|
|
|
|
|