Problem with this stored Procedure

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)

Problem with this stored Procedure

Author

Topic

wollk
Starting Member

1 Post

Posted - 2003-03-07 : 11:49:19

Hi,
this procedure have to update a table in SQL Server 2000!
When i run this procedure in the Query Analyzer it works well! But when i go to sql server to view the result the table remains empty!! What is wrong or have i forgotten anything?

Thank you!

CREATE PROCEDURE proc_VersandPauschTabelle_2
AS
DECLARE
@test_cursor CURSOR,
@var1 float,
@var2 int,
@var3 int,
@Zähler int,
@row_name CHAR(7),
@sql CHAR(100);

BEGIN

SET @test_cursor = CURSOR SCROLL DYNAMIC
FOR SELECT [Nebenkosten pro Klasse], Endeklasse, Beginnlotterie
FROM LotterieData.dbo.Debitor

OPEN @test_cursor ;

/*der erste Datenabruf vom Cursor*/
FETCH NEXT FROM @test_cursor INTO @var1,@var2,@var3;

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Zähler=1;
WHILE ( @zähler<=@var2)
BEGIN
SET @row_name='Klasse'+cast(@Zähler as varchar(3));
SET @sql='Insert into LotterieData.dbo.tblVersandPausch ( '+@row_name+', Lotterie) Values ('+cast(@var1 as varchar)+', '+cast(@var3 as varchar)+')';
PRINT @sql;
EXEC(@sql);
--update LotterieData.dbo.tblVersandPausch
SET @zähler=@zähler +1;
/*CURSOR weitersetzen*/
END
FETCH NEXT FROM @test_cursor INTO @var1,@var2,@var3;

END

CLOSE @test_cursor;
DEALLOCATE @test_cursor;
select * from LotterieData.dbo.tblVersandPausch
END
GO

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-07 : 12:49:12

What do you get when you run the following

SELECT MAX(Endeklasse)
FROM LotterieData.dbo.Debitor

Can you provide ddl for LotterieData.dbo.tblVersandPausch and LotterieData.dbo.Debitor. Also some sample data and expected results in tblVersandPaush. Someone might be able help you rewrite the cursor.

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-07 : 12:58:22

I didn't know you could define a cursor like that. I tired:

Declar @x Cursor

SET @X = CURSOR SCROLL DYNAMIC FOR SELECT * From sysobject

and it didn't work.

Also, Why Use cursors at all?

And why the Dynamic SQL?

Brett

8-)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-07 : 13:08:36

FROM BOL:

Sets the specified local variable, previously created with the DECLARE @local_variable statement, to the given value.

Syntax
SET { { @local_variable = expression }
| { @cursor_variable = { @cursor_variable | cursor_name
| { CURSOR [ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] }
]
}
} }
}

Arguments

D. Define a cursor with SET
This example uses the SET statement to define a cursor.

DECLARE @CursorVar CURSOR

SET @CursorVar = CURSOR SCROLL DYNAMIC
FOR
SELECT LastName, FirstName
FROM Northwind.dbo.Employees
WHERE LastName like 'B%'

OPEN @CursorVar

FETCH NEXT FROM @CursorVar
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @CursorVar
END

CLOSE @CursorVar
DEALLOCATE @CursorVar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources