Stored Proc Call in Stored Proc 
Author Message
 Stored Proc Call in Stored Proc

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 ??



Mon, 06 Jun 2005 01:00:30 GMT
 Stored Proc Call in Stored Proc

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


Quote:
> 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 ??



Mon, 06 Jun 2005 02:18:40 GMT
 Stored Proc Call in Stored Proc
Hi OJ,

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



Mon, 06 Jun 2005 17:34:34 GMT
 Stored Proc Call in Stored Proc
you're welcome. ;)

--
-oj


Quote:
> Hi OJ,

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



Tue, 07 Jun 2005 01:29:18 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. calling stored proc from stored proc via variable

2. Stored Proc Calling Another Stored Proc

3. Calling a Stored proc within a stored proc

4. How do I call a stored proc from inside another stored proc

5. Calling another stored proc within a stored proc

6. Calling a stored proc within a stored proc

7. a stored proc calling another stored proc

8. Using a stored proc to call another stored proc

9. How to call a stored Proc or Ext Stored proc /T-SQL UDF from VBScript

10. Calling Stored Proc from other Stored Proc

11. Saving the resultset from a stored proc being called in another stored proc

12. Persisting recordset from stored proc to XML re-executes stored proc


 
Powered by phpBB® Forum Software