Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Using a stored proc within a stored proc

    I want to determin if a user belongs to a certain group or not within a stored proc:

    Code:
    CREATE PROCEDURE usp_General_IsEstimator
    @cLogin sysname,
    @bEstimator bit
    
    AS
    
    DECLARE groups CURSOR FOR EXEC sp_helpuser @cLogin
    What I was planning on doing is to see if the user login returns the 'Estimating' group name using the sp_helpuser. If so, set @bEstimator = 1 else 0

    How can I accomplish this?

    I cannot use the cursor with the stored proc. How do I work with the recordset returned by the stored proc?

    Any ideas?

    Mike B

  2. #2
    Join Date
    Jul 2004
    Location
    Irvine, CA
    Posts
    7
    You can run the exec command to kick the result set into a temp table, then query the temp table for results. Have a look in the documentation under EXEC.

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by CobaltData
    You can run the exec command to kick the result set into a temp table, then query the temp table for results. Have a look in the documentation under EXEC.
    Thanks I got it!!

    Code:
    CREATE PROCEDURE usp_General_IsEstimator
    @cUserLogin sysname,
    @bEstimator bit OUTPUT
    AS
    
    CREATE TABLE #Temp(
    UserName sysname,
    GroupName sysname,
    LoginName sysname,
    DefDBName sysname,
    UserID smallint,
    SID smallint
    )
    	
    INSERT INTO #Temp EXEC sp_helpuser @cUserLogin
    
    IF EXISTS (SELECT GroupName FROM #Temp WHERE LoginName = @cUserLogin AND GroupName = 'Estimators')
    	SET @bEstimator = 1
    ELSE
    	SET @bEstimator = 0
    
    SELECT @bEstimator
    
    DROP TABLE #Temp
    GO
    Mike B
    Last edited by MikeB_2k4; 07-18-04 at 09:27.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •