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

    Join Date
    Apr 2012
    Posts
    15
    Rep Power
    0

    PHP MSSQL - sqlsrv stored procedure


    I created a microsoft sql stored procedure, and I am calling it from the php code below. I do not get any errors, but I do not get any output either. I have looked all over, but I am still fuzzy about how to do the WHILE statement to retrieve the variables I need from sql... I am hoping someone can take a look at the php code below (and the sql code below that) to see what I might be missing. Oh, and when I execute the stored procedure through SQL, it works fine and returns the data I expect. Thanks!

    PHP Code to retrieve results from MSSQL stored procedure

    PHP Code:

    /* Define the Transact-SQL query. Use question marks (?) in
     place of  the parameters to be passed to the stored procedure
     */ 

    $tsql_callSP "{call usp_sessions_set_terms( ?, ?, ? )}";  

    /* Define the parameter array. By default, the first parameter is
     an INPUT parameter. The second parameter is specified as an
     OUTPUT parameter. To ensure data type integrity, output 
    parameters should be initialized before calling the stored 
    procedure, or the desired PHPTYPE should be specified in the 
    $params array.*/ 

    $id_language $id_language
    $term ''
    $native_term ''

    $params = array(
    array(
    $id_language,SQLSRV_PARAM_IN),
    array(
    $termSQLSRV_PARAM_OUT), 
    array(
    $native_termSQLSRV_PARAM_OUT)              
    );  

    /* Execute the query. */ 
    $stmt3 sqlsrv_query$conn$tsql_callSP$params);  

    if( 
    $stmt3 === false ) {      
    echo 
    "Error in executing statement 3.\n";      
    die( 
    print_rsqlsrv_errors(), true)); }  

    /* Display the value of the output parameters. */  
    while (sqlsrv_fetch_object($stmt3)) {      
    // SET PARAMETERS - SET TERMS     
    echo $term
    }  

    /*Free the statement and connection resources. */ 
    sqlsrv_free_stmt$stmt3); 
    MSSQL Stored Procedure Code

    Code:
    USE [dbase] 
    GO 
    
    /****** Object:  StoredProcedure [dbo].[usp_sessions_set_terms]    
    "Script Date: 04/06/2013 16:51:58 ******/ 
    
    SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- 
    ALTER PROCEDURE [dbo].[usp_sessions_set_terms] 	
    -- Add the parameters for the stored procedure here 
    
    @id_language varchar (10) = '', 
    @term varchar(100) OUTPUT, 
    @native_term nvarchar(MAX) OUTPUT 
    
    AS 
    
    BEGIN 
    SET NOCOUNT ON;      
    
    -- Insert statements for procedure here 	
    SELECT  term, native_term FROM [usp].[dbo].[terms_data] 
    WHERE  	
    (id_language=@id_language) 
    ORDER BY term END
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,332
    Rep Power
    594
    When you fetch the row, you don't store it anywhere:
    PHP Code:
    while ($obj=sqlsrv_fetch_object($stmt3)) {      
    // SET PARAMETERS - SET TERMS     
    echo $obj->term

    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep Power
    0

    Thank you!!!!


    That was it - thanks so much!!!

    Originally Posted by gw1500se
    When you fetch the row, you don't store it anywhere:
    PHP Code:
    while ($obj=sqlsrv_fetch_object($stmt3)) {      
    // SET PARAMETERS - SET TERMS     
    echo $obj->term

    Last edited by laural4705; April 6th, 2013 at 07:52 PM. Reason: To close thread

IMN logo majestic logo threadwatch logo seochat tools logo