Help "Execute stored precedure"

Question

Post reply

Help "Execute stored precedure"

GIRODON

Valued Member

Points: 54
More actions
March 26, 2004 at 7:43 am

#107495

I have a table : MyTable (id, name)
I have a procedure fgirtest which is :
CREATE PROCEDURE fgirtest (@titles_cursor CURSOR VARYING OUTPUT) AS
SET @titles_cursor = CURSOR FOR SELECT * FROM chain
RETURN
GO
Then in another procedure, i want to do this :
DECLARE @MyCursor CURSOR, @name VARCHAR
EXEC fgirtest @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor INTO @name
INSERT INTO MyTable(name) VALUES(@name)
END
And nothing is done ... What can i do ? Is it possible ?
thks,

Viewing 9 posts - 1 through 9 (of 9 total)

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

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

Do you have any records in the chain table?

Does it have only a column named name?

You coud add prints inside the sp to know the values of variables.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

That won't work. See http://www.sommarskog.se/dynamic_sql.html#cursor how to declare dynamically a cursor.

But more important: Why do you use a cursor anyway?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

GIRODON Valued Member Points: 54 More actions · Answer 3

of course, i have a field "name" in my table "chain" ....

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

So what?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

GIRODON Valued Member Points: 54 More actions · Answer 5

I use a cursor, because I want to create a system of rules based on stored procedure which allows to the user to create him self stored procedures with existing procedure ...

So, in some case i need that a stored procedure returns a cursor ...

Is it clear ?

Antares686 SSC Guru Points: 125444 More actions · Answer 6

What is the ultimate goal here for your data? May be the wrong approach.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 7

Hi GIRODON,

I don't know if you have already taken a look at BOL, but if not please take a look at Example given under

stored procedures, CURSOR_STATUS

It will give a pretty good idea about the VARYING OUTPUT CURSOR.

Thanks

Prasad Bhogadi
www.inforaise.com

GIRODON Valued Member Points: 54 More actions · Answer 8

I've found !

CREATE PROCEDURE fgirtest(@my_cursor CURSOR VARYING OUTPUT)

AS

SET @my_cursor = CURSOR STATIC FOR SELECT name FROM chain

OPEN @my_cursor

RETURN

GO

DECLARE @MyCursor CURSOR

DECLARE @name VARCHAR(250)

EXEC fgirtest @MyCursor OUTPUT

FETCH NEXT FROM @MyCursor INTO @name

WHILE (@@FETCH_STATUS = 0)

BEGIN

INSERT into test(name) VALUES(@name)

FETCH NEXT FROM @MyCursor INTO @name

END