#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    4
    Rep Power
    0

    PHP and MS SQL (with sqlsrv driver) return value


    1 down vote favorite


    I'm working with a MS SQL database that was done by another developer. Im developing a website that interacts with that database. I need to use a stored procedure called LoginR that takes 3 input parameters (LoginName and Passwords) and returns the NickName in the third value. But also, has a "return" command that returns the ID of the user. Here is the procedure as I see on Microsoft SQL Server Managment Studio:

    Code:
    USE [myDataBase]
    GO
    
    DECLARE @return_value int,
            @NickName nvarchar(100)
    
    EXEC    @return_value = [dbo].[msp_LoginR]
            @LoginName = N'MyUser',
            @Password = N'MyPass',
            @NickName = @NickName OUTPUT
    
    SELECT  @NickName as N'@NickName'
    
    SELECT  'Return Value' = @return_value
    
    GO
    I can run perfectly the stored procedure in PHP in this way:

    PHP Code:
                $procedure "{call msp_LoginR( ?, ?, ?)}";
                
    $LoginName $_POST['user'];
                
    $Password $_POST['password'];
                
    $NickName '000000000000000000000000000000000000';
                
    $params = array( 
                    array(
    $LoginNameSQLSRV_PARAM_IN),
                    array(
    $PasswordSQLSRV_PARAM_IN),
                    array(
    $NickNameSQLSRV_PARAM_OUT)
                );

            
    $res sqlsrv_query$conMS$procedure$params); 
    And after doing that I got my variable $NickName with the correct value. But I can't get that 'Return Value' = @return_value that the Stored procedures send me and it's the user's ID. What I need to do?
  2. #2
  3. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,107
    Rep Power
    9644
    I would expect that you'd use the usual query/fetch pattern:
    PHP Code:
    $res sqlsrv_query($conMS$procedure$params);
    $row sqlsrv_fetch_array($resSQLSRV_FETCH_ASSOC);
    echo 
    $row["Return Value"]; 
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    4
    Rep Power
    0
    I did, but $row is null/false. I also tried fech_object and just fetch and nothing seems to work. However, $NickName is populated and the query doesn't give errors.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    4
    Rep Power
    0
    sqlsrv_next_result neither seems to work. I tried sqlsrv_num_fields an returns 0.
  8. #5
  9. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,005
    Rep Power
    4084
    Use the syntax
    PHP Code:
    //
    $procedure "{?=call msp_LoginR( ?, ?, ?)}"
    // 
    Then just bind the first parameter as an output parameter.
    PHP Code:
    $params = array(  
                    array(
    $ReturnValueSQLSRV_PARAM_OUT),
                    array(
    $LoginNameSQLSRV_PARAM_IN), 
                    array(
    $PasswordSQLSRV_PARAM_IN), 
                    array(
    $NickNameSQLSRV_PARAM_OUT
                ); 

    Comments on this post

    • DarkSlash agrees
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    4
    Rep Power
    0
    Perfect! That worked! Thanks!! Case closed

IMN logo majestic logo threadwatch logo seochat tools logo