I have problem in selecting a row in child sp which just got updated in parent SP.
MySP updates MyTestTable's MonkeyColumn, and right after it want to return newly updated row to the user by calling to the GetMonkey procedure, but for some reason monkeyColumn is still empty. I know that i can use Output parameters, but then MySP will only return output parameters and not the result of GetMonkey stored procedure. What have i done wrong?
Create PROCEDURE [dbo].[MySP]
AS
Begin
declare @testid uniqueidentifier
select @testid = [dbo].GetTestIDFromParams(@someValue1)
Update MyTestTable
set MonkeyColumn = 'monkey',
where id= @testid;
exec [dbo].GetMonkey @testid
END
GetMonkey contains :
Create PROCEDURE [dbo].[GetMonkey]
-- Add the parameters for the stored procedure here
@testid uniqueidentifier
AS
Begin
SET NOCOUNT ON;
Select MonkeyColumn from MyTestTable where id= @testid;
END