Calling a stored procedure from within a stored procedure

[RobS23]

I'm trying to utilise the sp_helpuser sp. I want to nest it within my own sp and retrieve a specific field.

ie. if this was a table i would

DECLARE @MyParam

Select @MyParam = sp_helpuser.Groupname

I could then go on to use the parameter @MyParam to retieve data from my own data.

ie.

SELECT groupID
FROM tblMySecurity
WHERE groupname = @myParam

Get the idea? Got an answer? let me know

Cheers

 

[tlbroadbent]

You cannot return data from the stored procedure to your variable directly. You can create a temporary table and insert the results of the SP into the table. You can then retrieve the data form the temp table.

INSERT #tbl
Exec sp_helpuser

Select @myparam=groupname from #t

Which version of SQL are you using? There are no groups in SQL 7 or higher. They have been replaced by roles. GroupName returned by sp_helpuser refers to roles and a user can have more than one role in a database. You'll need to modify your logic accordingly. You may also want to consider using the Is_Member function to deternine whether the current user is a member of the specified Microsoft Windows NT group or Microsoft SQL Server role.

You can also use sysusers and sysmembers table in conjuction with your security table. This may be better than using the SP and a temp table. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost