0

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

closed as unclear what you're asking by spaghettidba, Philᵀᴹ, Julien Vavasseur, Kin Shah, Paul White Mar 2 '16 at 17:14

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • It's awfully hard to understand what you're asking. What's your problem? The column is empty? What does empty mean? NULL? Empty string? What's your problem? Returning OUTPUT parameters? – spaghettidba Mar 2 '16 at 14:48
  • Have you tried sticking the SELECT in the original SP to see if there is a problem with the EXEC statement? My quick mockup works both ways without a problem which makes me think your not updating anything in the UPDATE – Duffy Mar 2 '16 at 15:41
1

The only scenario I can find that no results would be returned is if no row was updated. Architecturally speaking if that is actually your code and I would not bother making Stored Procedures that simplistic, just embed the SELECT query in the original Stored Procedure. Due to how SQL works if you start to get more complex with your queries trying to use certain Object Oriented habits by turning every duplicated or close to duplicated query into a Stored Procedure call can quickly become troublesome.

  • There was no row updated, because i messed up some logic. – Timsen Mar 2 '16 at 20:09

Not the answer you're looking for? Browse other questions tagged or ask your own question.