FETCH NEXT problem

Question

Post reply

FETCH NEXT problem

Ahlforn

SSC Rookie

Points: 37
More actions
April 30, 2004 at 5:58 pm

#107651

Hi
Im having a bit of problems with this script:
declare @sourcetable table (nr int, tekst varchar(10))
declare @desttable table (nr int, tekst varchar(10))
insert into @sourcetable values (1, 'hey')
insert into @sourcetable values (2, 'hey')
insert into @sourcetable values (3, 'hey')
insert into @sourcetable values (4, 'hey')
insert into @sourcetable values (5, 'hey')
DECLARE @my_cursor CURSOR SET @my_cursor = CURSOR LOCAL SCROLL FOR select * from @sourcetable
IF Cursor_Status('variable', '@my_cursor') <= 0
BEGIN
select 'error'
END ELSE BEGIN
FETCH @my_cursor INTO @desttable
END

It keeps telling me this:
Server: Msg 137, Level 15, State 1, Line 17
Must declare the variable '@desttable'.

Now this is only the start of a bigger script I need to develop, so there is a reason why I want it to insert into a local table variable. Can anyone please tell me what Im doing wrong here

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Peter P SSCrazy Points: 2777 More actions · Answer 1

You might try another way to do what you're doing besides cursors, but the following works:

declare @sourcetable table (nr int, tekst varchar(10))

declare @desttable table (nr int, tekst varchar(10))

declare @val1 int

declare @val2 varchar(10)

insert into @sourcetable values (1, 'hey')

insert into @sourcetable values (2, 'hey')

insert into @sourcetable values (3, 'hey')

insert into @sourcetable values (4, 'hey')

insert into @sourcetable values (5, 'hey')

DECLARE @my_cursor CURSOR SET @my_cursor = CURSOR LOCAL SCROLL FOR select * from @sourcetable

--Open Cursor

OPEN @my_cursor

FETCH NEXT FROM @my_cursor

INTO @val1,@val2

-- IF Cursor_Status('variable', '@my_cursor') <= 0

--Use a While Loop here to get all values

WHILE @@FETCH_STATUS = 0

BEGIN

IF Cursor_Status('variable', '@my_cursor') <= 0

BEGIN

select 'error'

END

ELSE

BEGIN

FETCH NEXT FROM @my_cursor

INTO @val1,@val2

INSERT INTO @desttable(nr,tekst)Select @val1,@val2

END --close the if construct

END --close the while loop

select * from @desttable

CLOSE @my_cursor

DEALLOCATE @my_cursor

Ahlforn SSC Rookie Points: 37 More actions · Answer 2

I do know that it is possible to fetch into local variables but i just wondered why it isn't possible to fetch into a local table variable when it is possible to fetch into a normal table.

I have solved my problem but what is the reason that it isn't possible to fetch into a local table variable?

Peter P SSCrazy Points: 2777 More actions · Answer 3

I noticed that too. According to MS documentation you can use a table variable just like a table exept, however, they may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable statements.

Maybe they should think of adding a third exception.

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 4

You cannot fetch into a table variable because (1) the type of the variables in a fetch statement must be compatible with the type of the columns from the select statement in the declaration of the cursor and (2) you cannot have a column of the table data-type.

Quotes from BOL:

"INTO @variablename: Allows data from the columns of a fetch to be placed into local variables.[...] The data type of each variable must either match or be a supported implicit conversion of the data type of the corresponding result set column."

"The table data type can be used only to define local variables of type table or the return value of a user-defined function."

Razvan