Running Microsoft SQL [MSSQL] Stored procedures in PHP
SP=Stored procedure
Running stored procedures using Microsoft SQL in PHP with MSSQL interface.
1.Create the Stored procedure in Microsoft SQL management studio and check in the query analyzer if the SP .
2.Create the MSSQL database connections in a PHP class or a PHP file
3.In Microsoft SQL management studio go to :
Database –> YourDatabaseName –> Programmability –> Stored procedures –> Execute the Stored Procedure
right click the procedure name -> Execute
SP execution window
This is the Code 
Get this code to a PHP String and Exicute it using mssql_query()
If the SP output is a reutrn value $row=mssql_fetech_array($result)
$row['Return Value'];
if its a variable use $row['Variable Name'];
Hey Sudantha,
Nice job!!
The only approach that worked for me!
It seems to be working but not at all for me. I’m doing the SELECT right after calling the SP but getting empty results.
SELECT @returncode, @returnstring Dumping Result: Array ( [computed] => [computed1] => )Those two params are declares as OUTPUT in the SP.
Any advice?
Thanks in advance!
Hi Thanks fir your comment, i think u are talking abt getting a normal query result from a SQL SELECT statement which is placed inside a Stored procedure
after 1
SELECT @returncode, @returnstring
again use these statements
SELECT @returncode
SELECT @returnstring
so you can get the results in PHP
Actually I don’t have access to “see” the database so that I don’t know how exactly the SP is written.
I guess the params ar OUTPUT type. This is what they gave me:
[icode]
* PROCEDURE p_funProcessTransaction
@code varchar(1),
@type varchar(10),
@returnstring varchar(50) output,
@returncode varchar(50) output
[/code]
And this is my code:
$q = "DECLARE @returnstring VARCHAR(50), @returncode VARCHAR(50) \n"; $q.= "EXEC "; $q.= "[dbo].[{$procedureName}] \n"; $q.= " @code = 3, \n"; $q.= " @type = 'E', \n"; <...other params here...> $q.= " @returnstring = '', \n"; $q.= " @returncode = '' \n"; $q.= "SELECT @returncode as retcode, @returnstring as retstring \n"; $result = mssql_query($q) or die(mssql_get_last_message().PHP_EOL); $row = mssql_fetch_assoc($result); echo PHP_EOL."Dumping Result:".PHP_EOL; print_r($row);The output is what I sent in the previous post.
I'm really messed with this! Any help is greatly appreciated.
Hi i think you are declaring the the SQL Variables
DECLARE @returnstring VARCHAR(50), @returncode VARCHAR(50)
can you do it inside the the SP ?
Like..
CREATE PROCEDURE name .. input parameters
DECLARE @returnstring VARCHAR(50), @returncode VARCHAR(50)
SQL Statement
SELECT @returnstring
SELECT @returncode
i think you cant execute 2 SQL statements at onces using MSSQL_QUERY()
Hope this works
Hey,
What you said is ALMOST what I have.
The variables are being SET (but not declared) INSIDE the SP, but for some reason if I don’t declare them in my query before EXECuting the SP I have an error that says
“Must declare the variable ‘@returncode’.”
Since the SP is not in my hands and it’s a bit hard to modify it would be best if we are really sure it’s absolutely needed to make it changes.
Anyway, if I shoot two SELECTs in the same query it doesn’t throws error, but it seems to obey just the first one:
DECLARE @returnstring VARCHAR(50), @returncode VARCHAR(50) EXEC [dbo].[p_funProcessTransaction] @code = 2, @type = 'E', ... @returnstring = 'XXX', @returncode = 'XX' SELECT GETDATE() as todayis SELECT @returncode as returncode, @returnstring as returnstring Dumping Result: Array ( [todayis] => Aug 19 2010 07:51AM )I’ve also tried by doing a SELECT over other variables declared inside the SP and got the same result: empty
This is the SP declaration:
i have no idea
hi, i have done this through Adodb Library in very simple manner…
$addProduct = $obj->ExecuteQuery(“Begin;DECLARE @ProductCode as varchar (100) ;EXEC CREATEPRODUCT’$pname’, ‘$price’, @ProductCode OUTPUT, ‘$merchantId’;select @ProductCode;End;”);
$productCode = $addProduct[0][0];
for more explanation you can visit this site..
http://developer99.blogspot.com/2011/07/calling-ms-sql-sp-from-php.html
@developer99 goodwork !