SQL Stored Procedure Cursor help needed please 1

[tamathaltarak]

Hi,

Can someone please help me and show me where I am going wrong with this.

I am creating a stored procedure that uses a cursor to execute an insert statement. What I can't seem to do is pass parameters to the cursor.
I end up with invalid object @sTable. Any ideas ???

CREATE PROCEDURE SP_MYPROCEDURE (@sTable SYSNAME) AS
DECLARE @x	INT
DECLARE @y	INT
DECLARE @z	INT

DECLARE my_cursor CURSOR FOR
	SELECT x, y, z FROM @sTable
OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @x, @y, @z
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	IF (@@FETCH_STATUS <> -2)
		
		INSERT INTO MyTable (x, y, z)
		VALUES (@x, @y, @z)
		
	FETCH NEXT FROM my_cursor INTO @x, @y, @z
END
CLOSE my_cursor

DEALLOCATE my_cursor
GO

Any help will be really appreciated.

Thanks in advance :-;

Tamath Altarak

 

[Sawatzky]

Dear Tamath,

I have no SQL-Server running, so I cannot figure it out but i assume:

could be your parameter is passed as a varchar and cannot be converted into sysname properly or has leading or trailing spaces.

HTH

regards

Astrid

 

[tlbroadbent]

You cannot use variables for object or column names in SQL Server. You must create and execute dynamic SQL.

CREATE PROCEDURE SP_MYPROCEDURE (@sTable SYSNAME) AS
DECLARE @x INT
DECLARE @y INT
DECLARE @z INT
DECLARE @sql varchar(200)

Set @sql='DECLARE my_cursor CURSOR FOR SELECT x, y, z FROM ' + @sTable

Exec(@sql)

OPEN my_cursor
.
.
. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tamathaltarak]

Thanks Terry. You pointed me on the right track