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