Adding member on a Role

Hi all,

I'm wondering if I can add a member on my role inside an SP.

something like this.

-- EXEC sp_addrolemember 'RoleName', 'UserName'

but i can't use the above statemnt inside a stored proc. I'm getting this error msg

Msg 15002, Level 16, State 1, Procedure sp_addrolemember, Line 19

The procedure 'sp_addrolemember' cannot be executed within a transaction.

Is there any other alternative way to add a member to a role inside an SP?

Thank you so much and have a nice day to all

My bad it is not inside a [stored proc] it is inside a [TRIGGER after INSERT]|||

You can commit the transaction inside the trigger before you execute sp_addrolemember, just put a COMMIT statement before the call to sp_addrolemember. This could have undesirable effects if there are other triggers, so be careful and test it thoroughly! You should probably make this trigger the last trigger with sp_settriggerorder, which will at least make sure that it never commits before other triggers fire.

The other possibly better option is to create a stored procedure that performs the insert and then calls sp_addrolemember, and make sure that you always use that sproc to insert into the table. Using sprocs exclusively for all data modifications is a good principle anyway.

adding column in resultset from SP

I have a sp: mysp_getstuff
it contains the following:

SELECT Adress,City FROM tblUserData WHERE UserName='john'

as you can see it returns 2 columns.
I also have another SP: mysp_GetNr. This sp returns an integer.

I want to call mysp_getnr from mysp_getstuff and add the result to the 2 columns in a column named 'Number'
So the resultset from mysp_getstuff should be:
Adress, City, Number (in which the number column contains the result from mysp_GetNr)

How can I do that?

Use an OUTPUT Parameter to get the value from mysp_getnr.

Declare @.num int

EXEC mysp_getnr @.params... @.num OUTPUT

Then, in your mysp_getstuff you can either return @.num as its OUTPUT param or along with the result set.

SELECT Adress,City,Number=@.num FROM tblUserData WHERE UserName='john'.