Strange behaviour in wrapper script

Question

Strange behaviour in wrapper script

edwardwill

SSCertifiable

Points: 5830
More actions
September 23, 2024 at 9:18 am

#4461445
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 3 days, 15 hours ago by edwardwill.

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

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

Phil Parkin SSC Guru Points: 246880 More actions · Answer 1

'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

edwardwill SSCertifiable Points: 5830 More actions · Answer 2

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."

Emperor100 SSCrazy Points: 2172 More actions · Answer 3

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

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

Phil Parkin SSC Guru Points: 246880 More actions · Answer 4

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

Johan Bijnens SSC Guru Points: 135321 More actions · Answer 5

"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

edwardwill SSCertifiable Points: 5830 More actions · Answer 6

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