Strange behaviour in wrapper script

  • We have a script to elevate a system user to Super Admin privilege, with a wrapper to elevate such a user to Company Super Admin.

    ALTER PROCEDURE [Common].[usp_ElevateUserToCompanySuperAdmin]
    @userId AS UNIQUEIDENTIFIER
    AS
    BEGIN;
    DECLARE @elevateUserToCompanySuperAdmin AS BIT = 1;
        
        --Execute elevate to super admin procedure forcing elevation to company super admin
        RETURN EXECUTE Common.usp_ElevateUserToSuperAdmin @userId = @userId, @elevateUserToCompanySuperAdmin = @elevateUserToCompanySuperAdmin;
    END;

    However, the script is failing to execute correctly; there is no error message but the UPDATE and INSERT statements inside the wrapped script are not executed.  If you run the wrapped script supplying the elevateUserToCompanySuperAdmin parameter directly, it runs properly.

    EXECUTE Common.usp_ElevateUserToSuperAdmin @userId = 'D9DF4399-1779-4195-F05A-08DCD3E72525' @elevateUserToCompanySuperAdmin = 1;

    Anyone have any ideas why this might be happening?

    • This topic was modified 1 month, 2 weeks ago by  edwardwill.
  • 'failing to execute correctly' is not detailed enough for people to help. Please elucidate … is there an error message?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Sorry, my bad.  I've updated the topic thus "there is no error message, but the updates and inserts inside the wrapped script are not executed."

  • Add raiserror or print to check which is the last step that your SP executed successfully.

    =======================================================================

  • edwardwill wrote:

    Sorry, my bad.  I've updated the topic thus "there is no error message, but the updates and inserts inside the wrapped script are not executed."

    Are there any temp tables or transactions involved? Do you have TRY ... CATCH blocks in place to bubble up any errors occurring in nested procs?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • "RETURN EXECUTE ..." ? ( I never used this consturction )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks for your input everyone.  When I get a moment I'll check some of these but I agree that RETURN EXECUTE is a bit odd ...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply