Using System SP with an INSERT INTO Statement 2

[Cheryl3D]

Hi:

I have been using a system stored procedure called 'sp_helprolemember' that I used within a user defined stored procedure called 'procARSRoles' within SQL Server 2000. When I execute the following user defined procedure that contains the system procedure, it works just fine:

CREATE PROCEDURE dbo.procARSRoles
@DbRole varchar(100)
As

SET NOCOUNT ON
--CREATE A TEMPORARY TABLE

CREATE TABLE #ARSRolesMembers(
DbRole varchar(100) NULL,
MemberName varchar (50) NULL,
MemberSID varchar(100) NULL
)

-- INSERT the values needed later into the temporary table

INSERT INTO #ARSRolesMembers(DbRole, MemberName, MemberSID)
EXEC sp_helprolemember
SELECT * FROM #ARSRolesMembers
where #ARSRolesMembers.DbRole = @DbRole

This stored procedure provides me with a list of the roles and members who belong to a specified role. It works great.

For example, I could run the following in Query Analyzer:

exec procARSRoles 'db_owners'

The result will display those members assigned to the 'db_owners' role.

I plan to generate a report using this stored procedure in an MS Access 2000 project (adp). I want to have the parameter that allows the user generating the report with the possibility to select one of the roles and see which members are assigned to the role.

But, I also want the users to be able to generate a report that would show 'All' of the roles and members.

Is it possible to write a separate T-SQL statement within the same stored procedure 'procARSRoles'?

I know that I need to slightly alter to INSERT INTO statement to get 'All' of the roles and their assigned members within the database. The altered statement would look like:

INSERT INTO #ARSRolesMembers(DbRole, MemberName, MemberSID)
EXEC sp_helprolemember
SELECT * FROM #ARSRolesMembers


Is it possible to do both within one stored procedure?

Or do I need to write code that in essence says:

if 'All' then

INSERT INTO #ARSRolesMembers(DbRole, MemberName, MemberSID)
EXEC sp_helprolemember
SELECT * FROM #ARSRolesMembers

else

INSERT INTO #ARSRolesMembers(DbRole, MemberName, MemberSID)
EXEC sp_helprolemember
SELECT * FROM #ARSRolesMembers
where #ARSRolesMembers.DbRole = @DbRole


Any help on this would be greatly appreciated.

Thanks,
Cheryl

 

[zhavic]

Let say, when @DbRole is NULL, then you want All roles, than you can use something like this:

INSERT INTO #ARSRolesMembers(DbRole, MemberName, MemberSID)
EXEC sp_helprolemember
SELECT * FROM #ARSRolesMembers
where @DbRole IS NULL OR #ARSRolesMembers.DbRole = @DbRole


Zhavic





---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.