Stored Proc Call in Stored Proc

Stored Proc Call in Stored Proc

Post by Say » Fri, 20 Dec 2002 02:00:30



Hi,

I have the following scenario.

Stored Proc A creates a record in table A for the given user.
Stored Proc B retrives the information from table A for the given
user.

When Proc B is called, I check whether the given user has any records
in table A, and if he DOES NOT, I call Proc A from within Proc B, so
that some dummy info is added for the given user.

The problem here is that the initial call to Proc B (that was suppose
to add the info and return the record), adds the info but returns an
empty recordset.
But if I execute Proc B again it returns the desired result.

Below is the code for Proc B:

BEGIN


(
   MyDocumentID int NOT NULL ,
   MyDocumentName nvarchar(256) NOT NULL ,
   Lvl int NOT NULL ,
   Path nvarchar(500) NOT NULL
)

DECLARE











        FROM tblMyDocuments


          BEGIN
                BEGIN TRAN
-- creating a root element.

                COMMIT TRAN    

          END


                SELECT MyDocumentID, MyDocumentName, Lvl, Path

                ORDER BY Path


        SELECT N'<folders>'

        DECLARE curElement CURSOR FAST_FORWARD LOCAL FOR
                SELECT MyDocumentID, MyDocumentName, lvl, path

                ORDER BY path

        OPEN curElement

        FETCH NEXT FROM curElement


                BEGIN

                                BEGIN


                                                BEGIN
                                                        SELECT N'</folder>'

                                                END




                                END

                        FETCH NEXT FROM curElement

                END

        CLOSE curElement
        DEALLOCATE curElement



                BEGIN
                        SELECT N'</folder>'

                END

        SELECT N'</folders>'

        RETURN (0)

Its as the tabel does not get updated till after the proc execution
finishes. Is there anyway to solve this problem ??

 
 
 

Stored Proc Call in Stored Proc

Post by oj » Fri, 20 Dec 2002 03:18:40


1. What does "stp_DocumentsAddFolder" do.
2. What does "dbo.fnctDocuments_GetFolderTree" do.

If you post more info (ddl+sample data+code) we might be able to help.

--
-oj
http://www.rac4sql.net


> Hi,

> I have the following scenario.

> Stored Proc A creates a record in table A for the given user.
> Stored Proc B retrives the information from table A for the given
> user.

> When Proc B is called, I check whether the given user has any records
> in table A, and if he DOES NOT, I call Proc A from within Proc B, so
> that some dummy info is added for the given user.

> The problem here is that the initial call to Proc B (that was suppose
> to add the info and return the record), adds the info but returns an
> empty recordset.
> But if I execute Proc B again it returns the desired result.

> Below is the code for Proc B:

> BEGIN


> (
>    MyDocumentID int NOT NULL ,
>    MyDocumentName nvarchar(256) NOT NULL ,
>    Lvl int NOT NULL ,
>    Path nvarchar(500) NOT NULL
> )

> DECLARE











> FROM tblMyDocuments


>   BEGIN
> BEGIN TRAN
> -- creating a root element.


> COMMIT TRAN

>   END


> SELECT MyDocumentID, MyDocumentName, Lvl, Path

> ORDER BY Path


> SELECT N'<folders>'

> DECLARE curElement CURSOR FAST_FORWARD LOCAL FOR
> SELECT MyDocumentID, MyDocumentName, lvl, path

> ORDER BY path

> OPEN curElement

> FETCH NEXT FROM curElement


> BEGIN

> BEGIN


> BEGIN
> SELECT N'</folder>'

> END




> END

> FETCH NEXT FROM curElement

> END

> CLOSE curElement
> DEALLOCATE curElement



> BEGIN
> SELECT N'</folder>'

> END

> SELECT N'</folders>'

> RETURN (0)

> Its as the tabel does not get updated till after the proc execution
> finishes. Is there anyway to solve this problem ??


 
 
 

Stored Proc Call in Stored Proc

Post by Say » Fri, 20 Dec 2002 18:34:34


Hi OJ,

Thanks for your interest to help, but I got the problem solved :-)

 
 
 

Stored Proc Call in Stored Proc

Post by oj » Sat, 21 Dec 2002 02:29:18


you're welcome. ;)

--
-oj


Quote:> Hi OJ,

> Thanks for your interest to help, but I got the problem solved :-)