Re: Cursor Error...

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 04/01/05 

Date: Fri, 1 Apr 2005 08:14:12 -0600

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')

Relevant Pages

  • Re: Advice on SBS suitability
    ... Javier [SBS MVP] ... "Marc" wrote in message ... > will be capturing data and synchronising with SQL there ... > (some of which will be pulled from the SQL server) ...
    (microsoft.public.windows.server.sbs)
  • Re: MSSqlServerAdHelper
    ... Andy. ... "marc" wrote in message ... > I try to add a SQL Server to Active Directory and get ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL command to ROLLBACK
    ... Wayne Snyder, MCDBA, SQL Server MVP ... community of SQL Server professionals. ... "Marc" wrote in message ... can I rollback changes done to the database? ...
    (microsoft.public.sqlserver.programming)
  • Re: DataType for Column is an Auto Incrementing
    ... Read up on the IDENTITY property within Books Online (within the SQL Server ... program group). ... "Marc" wrote in message ...
    (microsoft.public.sqlserver.programming)
  • Re: us date format
    ... I don't know the context where Steve recommended using CLng, ... However, even if your front-end is linked to SQL Server, there's no reason why you can't use this "trick" to set the default value for a field. ... clng method when concatenating a dynamic SQL string. ... it to string when concatenating whith the dynamic SQL string? ...
    (microsoft.public.access.formscoding)