mssql_fetch_array

mssql_data_seek

Last updated: Fri, 10 Oct 2008

mssql_execute

(PHP 4 >= 4.0.7, PHP 5, PECL odbtp:1.1.1-1.1.4)

mssql_execute — Executes a stored procedure on a MS SQL server database

Description

mixed mssql_execute ( resource $stmt [, bool $skip_results ] )

Executes a stored procedure on a MS SQL server database

Parameters

stmt: Statement handle obtained with mssql_init().
skip_results: Whenever to skip the results or not.

Examples

Example #1 mssql_execute() example


<?php
// Create a new statement
$stmt = mssql_init('NewBlogEntry');

// Some data strings
$title = 'Test of blogging system';
$content = 'If you can read this, then the new system is compatible with MSSQL';

// Bind values
mssql_bind($stmt, '@author',    'Felipe Pena',  SQLVARCHAR,     false,  false,     60);
mssql_bind($stmt, '@date',      '08/10/2008',   SQLVARCHAR,     false,  false,     20);
mssql_bind($stmt, '@title',     $title,         SQLVARCHAR,     false,  false,     60);
mssql_bind($stmt, '@content',   $content,       SQLTEXT);

// Execute the statement
mssql_execute($stmt);

// And we can free it like so:
mssql_free_statement($stmt);
?>

Notes

Note: If the stored procedure returns parameters or a return value these will be available after the call to mssql_execute() unless the stored procedure returns more than one result set. In that case use mssql_next_result() to shift through the results. When the last result has been processed the output parameters and return values will be available.


In reply to: mark dot vanrossum at bris dot ac dot uk



A much simpler solution that allows you to keep the config in the PHP script is to force the TDS version by using putenv before you connect.



<?PHP

  putenv('TDSVER', '8.0');

  $dbc = mssql_connect('123.123.123.123:1433', 'user', 'pass');

?>

Anton Schattenfeld
02-Aug-2007 07:59


To get info about table structure you can use such a query:



SELECT

  column_name,

  data_type,

  character_maximum_length,

  numeric_precision,

  column_default,

  is_nullable

FROM

  information_schema.tables t 

  INNER JOIN

  information_schema.columns c 

  ON 

    t.table_catalog = c.table_catalog AND 

    t.table_schema = c.table_schema AND 

    t.table_name = c.table_name

WHERE     

  (c.table_name = 'TABLE_NAME')

mark dot vanrossum at bris dot ac dot uk
03-Jul-2007 05:47


I was pulling my hair out getting the error:

  "stored procedure execution failed"

when trying to run mssql_execute but you could run:

  $results = mssql_query('sp_test'); 

fine.



I was connecting using the string:

  $dbserver="xxx.xxx.xxx.xxx:1433";

  $cn = mssql_connect($dbserver, $dbuser, $dbpass);



where xxx is the IP address.



It seems that this doesn't work, you need to do the following:

edit your freetds.conf file and add the connection in here, eg:

[YourServer]

        host =xxx.xxx.xxx.xxx

        port = 1433

        tds version = 8.0



Then try and connect as:

$cn = mssql_connect('YourServer', $dbuser, $dbpass);



And it should work.  No idea why it doesn't work before, took me hours to find this out!

Manda Krishna Srikanth
17-Jan-2007 08:56


While using stored procedures on SQL EXPRESS (and perhaps on SQL Server), you have to specify the column names in SELECT, instead of asterisk (*). Or else you will get some big Unicode error.



That is, instead of "select * from table",

use "select col1, col2 from table".



One more important thing, Before using mssql_execute, you MUST AND SHOULD use mssql_init. mssql_init will generate the MS Sql statement resource, which will be taken as input by mssql_execute. Here is an example,



<?php

if($conn = mssql_connect('localhost\SQLEXPRESS', 'krishna', 'srikanth')) echo 'Connected to SQLEXPRESS';



if(mssql_select_db("Northwind",$conn)) echo 'Selected DB: Northwind<BR>';



$sql_statement =  mssql_init("[Ten Most Expensive Products]", $conn);

$result=mssql_execute($sql_statement);



while ($row = mssql_fetch_assoc($result))

    print_r($row); 

?>

stuhood at gmail doooot com
05-Dec-2006 12:55


If you need to get Output params from your stored procedure, make sure to use FreeTDS > 0.6.4... it has a bug that prevents some Output params from being set.

mpoletto at gmail dot com
21-Feb-2005 11:04


The constant SQLINT4 is not working with datetime. Try using SQLVARCHAR.

SQL dot User at Yandex dot Ru
12-Jul-2004 09:38


To receive output parameter from the procedure which returns one or several recordsets, try this code:



...

mssql_bind($my_procedure, "@OutputParameter", SQLVARCHAR, true);

$result = mssql_execute($my_procedure);

while(mssql_next_recordset($result)) {

## do something

}



after listing last recordset output parameter will be available (strange...).



If you do not need output recordsets, just parameters, try this:



mssql_bind($my_procedure, "@OutputParameter", SQLVARCHAR, true);

$result = mssql_execute($my_procedure, true);



P.S. Tested on PHP 4.3.5.

marco dot carvalho at NOSPAM dot uni-yoga dot org dot br
07-Jun-2004 02:00


<?PHP 



/*

<font color="#007f00">

Gets all fields properties from table/stored procedure in <i>systables</i> and stores

in <b>$GLOBALS['MSSQL_PROP_'.$object]</b><br>

Returns: <b>True</b> or <b>false</b>

</font>

*/



function mssql_get_all_fields_properties($object){

   $res=mssql_query("select colorder,name,xtype,length,xprec from syscolumns where id = object_id('$object')");

   if($res){

     while(($tmp = mssql_fetch_assoc($res))){

       $ret[$tmp['colorder']] = $tmp;

     }

     $GLOBALS['MSSQL_PROP_'.$object] = $ret;

     return true;

   }

   else return false;

}



/*

<font color="#007f00">

Gets <b>$field</b> properties from <b>$object</b> table/stored procedure in <i>systables</i> and stores

in <b>$GLOBALS['MSSQL_PROP_'.$object]</b><br>

Returns: <b>Array of properties</b> or <b>false</b> if some error occured.

</font>

*/



function mssql_get_field_properties($object,$field)

{

   if(!isset($GLOBALS['MSSQL_PROP_'.$object]) ) 

     if(!mssql_get_all_fields_properties($object)) 

       return false;

   

   foreach($GLOBALS['MSSQL_PROP_'.$object] as $prop){

     if($prop['name'] == $field) return $prop;

   }

}



/*

<font color="#007f00">

Gets <b>$field</b> length from <b>$object</b> table/stored procedure in <i>systables</i> and stores

in <b>$GLOBALS['MSSQL_PROP_'.$object]</b><br>

Returns: <b>length</b> or <b>false</b> if some error occured.

</font>

*/

function mssql_get_field_length($object,$field)

{

  if(!isset($GLOBALS['MSSQL_PROP_'.$object]) ) 

     if(!mssql_get_all_fields_properties($object)) 

       return false;



   foreach($GLOBALS['MSSQL_PROP_'.$object] as $prop){

     if($prop['name'] == $field) return $prop['length'];

   }

}



/*

<font color="#007f00">

Gets <b>$field</b> xprec from <b>$object</b> table/stored procedure in <i>systables</i> and stores

in <b>$GLOBALS['MSSQL_PROP_'.$object]</b><br>

Returns: <b>length</b> or <b>false</b> if some error occured.

</font>

*/

function mssql_get_field_length($object,$field)

{

  if(!isset($GLOBALS['MSSQL_PROP_'.$object]) ) 

     if(!mssql_get_all_fields_properties($object)) 

       return false;



   foreach($GLOBALS['MSSQL_PROP_'.$object] as $prop){

     if($prop['name'] == $field) return $prop['xprec'];

   }

}



/*

<font color="#007f00">

Gets <b>$field</b> xprec from <b>$object</b> table/stored procedure in <i>systables</i> and stores

in <b>$GLOBALS['MSSQL_PROP_'.$object]</b><br>

Returns: <b>length</b> or <b>false</b> if some error occured.

</font>

*/

function mssql_get_field_colorder($object,$field)

{

  if(!isset($GLOBALS['MSSQL_PROP_'.$object]) ) 

     if(!mssql_get_all_fields_properties($object)) 

       return false;



   foreach($GLOBALS['MSSQL_PROP_'.$object] as $prop){

     if($prop['name'] == $field) return $prop['colorder'];

   }

}



?>

marco dot carvalho at NOSPAM dot uni-yoga dot org dot br
07-Jun-2004 01:57


<?PHP 



/*This functions will help you to get SQLTYPES direct from systypes and you can associate them with PHPSQLTYPES. */



/* <font color="#007f00">Associete PHP types with systypes..xtypes,

   see <b>mssql_get_types()</b> </font>*/



if(!defined('dbMSSQL_Types')) 

{

    define('dbMSSqlTypes',1);

    $MSSQL_types[127] = SQLINT4;    /* bigint*/ 

    $MSSQL_types[104] = SQLBIT;     /* bit*/ 

    $MSSQL_types[175] = SQLCHAR;    /* char*/ 

    $MSSQL_types[56]  = SQLINT2;    /* int*/ 

    $MSSQL_types[52]  = SQLINT2;    /* smallint*/ 

    $MSSQL_types[35]  = SQLTEXT;    /* text*/ 

    $MSSQL_types[48]  = SQLINT1;    /* tinyint*/ 

    $MSSQL_types[167] = SQLVARCHAR; /* varchar*/ 

    $MSSQL_types[62]  = SQLFLT8;    /* float*/ 

    $MSSQL_types[173] = SQLVARCHAR; /* binary*/         // Adaptation

    $MSSQL_types[61]  = SQLINT4;    /* datetime*/       // Adaptation

    $MSSQL_types[106] = SQLFLT8;    /* decimal*/        // Adaptation

    $MSSQL_types[34]  = SQLVARCHAR; /* image*/          // Adaptation

    $MSSQL_types[60]  = SQLFLT8;    /* money*/          // Adaptation

    $MSSQL_types[239] = SQLCHAR;    /* nchar*/          // Adaptation

    $MSSQL_types[99]  = SQLTEXT;    /* ntext*/          // Adaptation

    $MSSQL_types[108] = SQLFLT8;    /* numeric*/        // Adaptation

    $MSSQL_types[231] = SQLVARCHAR; /* nvarchar*/       // Adaptation

    $MSSQL_types[59]  = SQLFLT8;    /* real*/           // Adaptation

    $MSSQL_types[58]  = SQLINT4;    /* smalldatetime*/  // Adaptation

    $MSSQL_types[122] = SQLFLT8;    /* smallmoney*/     // Adaptation

    $MSSQL_types[98]  = SQLVARCHAR; /* sql_variant*/    // Adaptation

    $MSSQL_types[189] = SQLINT4;    /* timestamp*/      // Adaptation

    $MSSQL_types[165] = SQLVARCHAR; /* varbinary*/      // Adaptation

}



/* <font color="#007f00">Gets current connection systypes and shows this:



$MSSQL_types[systypes..xtype] = PutPHPSqlTypeHere  // Name_in_systypes //



</font>*/

function mssql_get_types(){

   $res = mssql_query('select name,xtype from systypes');

   echo('<pre><CODE>');

   while(($val = mssql_fetch_assoc($res))){

      echo('$MSSQL_types['.$val['xtype']."]\t= ;\t/* ".$val['name']." */\n");

   }

   print_r(phpinfo(INFO_VARIABLES));

   echo('</CODE></pre>');

}



?>

iqq-pp at extreme dot ro
30-Oct-2003 03:01


php version 4.3.2



Take care when using stored procedures returning multiple results, seems that if the first result is empty, the pointer will be automatically moved to the next result. As in this example:



CREATE PROCEDURE test

AS

SELECT 0 as zero WHERE 0 = 1

SELECT 1 as one

GO



After executing the stored procedure, mssql_num_rows will report one, ignoring the first result.

eliseo at olografix dot org
12-Oct-2003 10:23


After many attempt I resolved the return output of a store procedure on Win2003 box, MSSQL7 and PHP 4.3.

I have problem to process the result from store procedure strCheckUser, and I must to set a R variable, that must be returned from the last select operation (Select @R as R) see below.



----------------------------

/*

Store procedure to CheckUser Exist

*/

CREATE PROC strCheckUser  

(

@AccountLO varchar(20) , 

@PasswordLO varchar(20)

)

AS

BEGIN

DECLARE @R INT

IF (SELECT count(*) as count FROM tbl_users WHERE AccountLO = @AccountLO and PasswordLO=@PasswordLO

) = 0

SET @R = '0'

END



BEGIN

IF (SELECT count(*) as count FROM tbl_users WHERE AccountLO = @AccountLO  and PasswordLO=@PasswordLO

) = 1

SET @R = '1'



END



Select @R as R



GO

-------------------------



This is the php page

<?



.....

.....

$AccountLO="myuser";

$PassowrdLO="mypass";



$result=mssql_query("strCheckUser ".$AccountLO.", ".$PasswordLO.""); 

//echo gettype($result);

$arr = mssql_fetch_assoc($result);        

echo $arr["R"]; 



......

......

?>



No $arr["R"] print 1 if the user exist and 0 if no exist



Thanks to duarte at uma dot pt for the suggestion

Bye eliseo@olografix.org

gstratfordATdas.ca
06-Aug-2003 01:25


The easiest way to use a stored procedure is:



$Result = mssql_query("StoredProcedureName Var1, Var2, Var3...");



$Result is then just like any other result set. You can get the output parameters by:



$arr = mssql_fetch_row($Result);



$OutputParam1 = $arr[0];

$OutputParam2 = $arr[1];

brian_caughlin at hotmail dot com
06-Aug-2003 11:04


Regarding Output Parameters and RETVAL: A change that appears to have begun around 4.3.



According to the documentation and previously posted comments, if a stored procedure returns only one Recordset, you could retrieve the RETVAL and Output Params right away.  THIS IS NO LONGER THE CASE.  Beginning around 4.3, you must always use the mssql_next_result() function if any recordset is returned at all.



If you consider the example posted below by fjortizATcomunetDOTes on 26-Dec-2001...



[...]



// Execute the Stored Proc

$result=mssql_execute($stmt); 



// Get the recordset

$arr=mssql_fetch_row($result); 

print ("Answer: " . $arr[0] . "

" ); 



// NEW for 4.3: Switch to the next Recordset

// Since there was only one recordset, it will return false...

mssql_next_result($result);



// And now RETVAL and Output Params are accessible...

print ("RETVAL = $val ; intval = $ival ; floatval = $fval ; string = $sval"); 



[...]



There is also another way, and that is to use a new optional skip parameter on the execute.



mssql_execute($stmt, true);



This appears to ignore any recordsets, allowing you to get at the retval and output parameters immediately.



For more information, please see Bug #21089.

fjortizATcomunetDOTes
26-Dec-2001 06:01


After initializing a stored procedure 


with mssql_init, and binding all the 


parameters (and return value if needed) 


with mssql_bind, you can execute the 


statement with mssql_execute.





Parameters:


- stmt: statement resource obtained with 


mssql_init.





From here, you can use any of the other 


mssql_* functions to retrieve the 


recordsets as if you had called 


mssql_query. Any T-SQL error will also 


be reported in the same way. The 


variables passed by reference for OUTPUT 


and RETVAL parameters will be filled 


with the right values.





Now, an example:





if we have this procedure:





CREATE PROCEDURE [procedure]


(


   @sval varchar(50) OUTPUT,


   @intval int OUTPUT, 


   @floatval decimal(6,4) OUTPUT


) AS





if @intval is null


    select '@intval is null' as answer


else     


    select '@intval is NOT null' as answer





set @sval='Hello ' + @sval 


set @intval=@intval+1


set @floatval=@floatval+1





return 10





We can use this PHP code:





<?php





$conn=mssql_connect("myhost","user","pwd");





if ($conn) {


    mssql_select_db("mydb",$conn);


    


    $stmt=mssql_init("procedure",$conn);


    mssql_bind($stmt,"RETVAL",&$val,SQLINT4);





    $ival=11;


    $fval=2.1416;


    $sval="Frank";


    


    mssql_bind($stmt,"@sval",&$sval,SQLVARCHAR,TRUE);    


    mssql_bind($stmt,"@intval",&$ival,SQLINT4,TRUE);


    mssql_bind($stmt,"@floatval",&$fval,SQLFLT8,TRUE);


    


    $result=mssql_execute($stmt);





    $arr=mssql_fetch_row($result);


    print ("Answer: " . $arr[0] . "<br>" );


    print ("RETVAL = $val ; intval = $ival ; floatval = $fval ; string = $sval");


    


    mssql_close($conn);


}


else print("ooops!");


?>








Hope it helps. Good luck!

add a note