locked
Select within Stored Procedure RRS feed

  • Question

  • Hi,
    Whats the correct syntax for this in a stored procedure.
    	select @useridHigh, @userBalHigh = UserID,MAX(Balance)
    		from [User]
    		where LastName = 'Tom'
    		group by UserID

    Thanks

    Rebekah

    Thursday, May 19, 2011 9:17 AM

Answers


  • HI Rebekah!

    You may also use the below query to get the desired result.

    SELECT   @useridHigh = userid,
             @userBalHigh = Max(balance)
    FROM     [user]
    WHERE    lastname = 'Tom'
    GROUP BY userid 

    Please let me know if this doesn’t work for you. Hope I have answered you correctly

    Thanks, Hasham

    Thursday, May 19, 2011 9:23 AM
    Answerer

All replies


  • HI Rebekah!

    You may also use the below query to get the desired result.

    SELECT   @useridHigh = userid,
             @userBalHigh = Max(balance)
    FROM     [user]
    WHERE    lastname = 'Tom'
    GROUP BY userid 

    Please let me know if this doesn’t work for you. Hope I have answered you correctly

    Thanks, Hasham

    Thursday, May 19, 2011 9:23 AM
    Answerer
  • Thanks for your speedy reply
    Thursday, May 19, 2011 9:25 AM
  • Could be in similar lines  like this :

     

    DECLARE @useridHigh INT
    DECLARE @userBalHigh INT 
    DECLARE @MAXBALANCE INT
    
    select @useridHigh = useridhigh FROM [User] where LastName = 'Tom'
    SELECT @userBalHigh = UserID FROM [User] where LastName = 'Tom'
    SELECT @MAXBALANCE = MAX(Balance) FROM [User] where LastName = 'Tom' GROUP BY UserId 
    
    SELECT @useridHigh,@userBalHigh,@MAXBALANCE
    	
    
     


    Happy to help! Thanks. Regards and good Wishes, Deepak.
    Thursday, May 19, 2011 9:26 AM
  • i assume that want to get the details of the user (id & balance) of all users whoes last name is Tom

    CREATE PROCEDURE usp_Temp
    
    AS
    
    BEGIN
    
    
    
    	DECLARE @UserId INT = 0,
    
    			@MaxBalance INT
    
    			
    
    	SELECT
    
    			TOP 1 
    
    			@UserId = A.UserId , @MaxBalance = MAX(Balance)
    
    	FROM
    
    			dbo.[User] A
    
    	WHERE
    
    			A.LastName = 'Tom'
    
    	GROUP BY
    
    			A.UserId
    
    			
    
    	SELECT @UserId AS [UserId], @MaxBalance AS [MaxBalance]
    
    END
    
    
    
    
    
     
    
    
    Hope the above helps

    • Proposed as answer by bobragland Thursday, May 19, 2011 9:39 AM
    Thursday, May 19, 2011 9:36 AM