passing parameters to stored procedure SQL server from PHP

Question

i want to pass my params into stored procedure in SQL server but it seems like i can't do this nicely

this is my php code for executing procedure in SQL server

$area = $_GET['area'];
$SQL = "Read_Location_Area";
$result = sqlsrv_query($conn, $SQL);

and this is the script for SQL server procedure

USE [DP_Rozakana]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Read_Location_Area]
as
BEGIN
select * from Locations where Areas = '$area'
END

finally the sql response for searching data from "Locations" table where Areas like $area(string), not a value from PHP

you need to pass a parameter to the procedure. use sqlsrv_query in php and your procedure should have a parameter which should be used internally — ughai
– ughai, Commented Aug 17, 2015 at 8:50
First learn how to create a stored proceedure with parameters — RiggsFolly
– RiggsFolly, Commented Aug 17, 2015 at 8:53
probably a duplicate of "How to call stored procedure by using PHP and SQL Server" — SouthL
– SouthL, Commented Aug 17, 2015 at 8:57

alpakyol · Accepted Answer · 2015-08-17 12:05:02Z

0

First, you should check official SQLSRV Driver API Reference, you can find all answers about sqlsrv library.

Now answering your question,

Your $SQL string should be like "{CALL [dbo].[Read_Location_Area]}".

However, you will pass a parameter to the procedure, so your procedure should be:

USE [DP_Rozakana]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Read_Location_Area]
as
    @area NVARCHAR(MAX) -- type of Areas column
BEGIN
    select *
    from   Locations
    where  Areas = @area
END

Then in php, your code eventually looks like:

$area = $_GET['area'];
$params = array(
    array($area, SQLSRV_PARAM_IN)
);
$SQL = "{CALL [dbo].[Read_Location_Area](?)}";
$result = sqlsrv_query($conn, $SQL, $params);

After these steps you should be ready to fetch results.

answered Aug 17, 2015 at 12:05

alpakyol

2,4542 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

passing parameters to stored procedure SQL server from PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related