SQL Server Procedure not returning output in PHP

Question

I'm trying to execute an SQL procedure and return values in php. My code is as below :

<?PHP
include "conn.php";
    $myquery= "exec CR_Report @System='Automated Forms'";
    $fetched=sqlsrv_query($conn,$myquery);
    if( $fetched === false ) { die( print_r( sqlsrv_errors(), true ));}
        while($res=sqlsrv_fetch_array($fetched,SQLSRV_FETCH_ASSOC))
        {
            echo $res['ID'];
        }
?>

Procedure :

CREATE procedure [dbo].[CR_Report]
    @Department varchar(60)  = Null,
    @System varchar(30) = Null,
    @Fromdate date = Null,
    @Todate date = Null
AS
IF @System is Null and 
 @Fromdate is Null and
 @Todate is Null
 BEGIN
 Create table #Temp_ConfigRequest_Department
(
ID int,
Form_No varchar(25),
App_Date smalldatetime,
System  varchar(30)
)

delete #Temp_ConfigRequest_Department

insert into  #Temp_ConfigRequest_Department

SELECT * FROM [SUAF].[dbo].[Config_Request] where Admin_Department = @Department

select * from #Temp_ConfigRequest_Department

DROP TABLE #Temp_ConfigRequest_Department

end
IF @Department is Null and 
 @Fromdate is Null and
 @Todate is Null
 BEGIN

 Create table #Temp_ConfigRequest_System
(
ID int,
Form_No varchar(25),
App_Date smalldatetime,
System  varchar(30)
)

delete #Temp_ConfigRequest_System

insert into  #Temp_ConfigRequest_System

SELECT * FROM [SUAF].[dbo].[Config_Request] where System = @System

select * from #Temp_ConfigRequest_System

DROP TABLE #Temp_ConfigRequest_System

end
IF @Department is NULL and
@System is NULL
BEGIN 
 Create table #Temp_ConfigRequest_Date
(
ID int,
Form_No varchar(25),
App_Date smalldatetime,
System  varchar(30)
)

delete #Temp_ConfigRequest_Date

insert into  #Temp_ConfigRequest_Date

SELECT * FROM [SUAF].[dbo].[Config_Request] where convert(date, App_Date, 103) Between @Fromdate and @Todate

select * from #Temp_ConfigRequest_Date

DROP TABLE #Temp_ConfigRequest_Date

end
IF  @Department is not Null and
    @System is not  Null and
    @Fromdate is not  Null and
    @Todate is not  Null
    BEGIN 
Create table #Temp_ConfigRequest_All
(
ID int,
Form_No varchar(25),
App_Date smalldatetime,
System  varchar(30)
)

delete #Temp_ConfigRequest_All

insert into  #Temp_ConfigRequest_All

SELECT * FROM [SUAF].[dbo].[Config_Request] where convert(date, App_Date, 103) Between @Fromdate and @Todate and Admin_Department = @Department and System = @System

select * from #Temp_ConfigRequest_All

DROP TABLE #Temp_ConfigRequest_All

end

It doesn't return any value and also no errors are logged in the error log. I tried testing the sql query in the database and it does return record. To test the connection, I wrote a code for another procedure in the same file, and it is returning values. The second code is as below :

<?PHP
$myquery="exec Configuration_Request @IDtemp='4275'";   
    $fetched=sqlsrv_query($conn,$myquery) ; 
    if( $fetched === false ) { die( print_r( sqlsrv_errors(), true ));}
        while($res=sqlsrv_fetch_array($fetched,SQLSRV_FETCH_ASSOC))
        {
            echo $res['Form_No'];
            echo $res['Administrator'];
        }
?>

Procedure :

create procedure Configuration_Request (@IDtemp int)
as
begin
select * from Config_Request where ID=@IDtemp
end

Where is the issue? Why is the 1st code not returning any values? Would appreciate any help/suggestion.

Does the query in the first stored procedure return a column called 'ID'? — Simon Sellick
– Simon Sellick, Commented Jun 20, 2016 at 13:15
I'm not a PHP expeert but the only difference I can see except for the stored proc name is that the second example doesn't include conn.php. (Also, the indentation looks a bit haywire, not that that affects the execution.) If no one suggests anything better, could you post the source of the two stored procedures? — Simon Sellick
– Simon Sellick, Commented Jun 20, 2016 at 13:23
@SimonSellick They both are in the same file, that's why the 2nd one doesn't contain a conn.php. I can post it.. but both of them work when executed in sql server.. — SR1092
– SR1092, Commented Jun 20, 2016 at 13:39
Holy cow....that procedure is a good example of way over complicating things. Why are you creating a temp table, then immediately deleting it? Then you do a simple insert into it, then select the data just inserted, then drop the temp table. This should be nothing more complicated than a simple select statement. — Sean Lange
– Sean Lange, Commented Jun 20, 2016 at 14:02

Sean Lange · Accepted Answer · 2016-06-20 14:03:47Z

1

You could GREATLY simplify that procedure into this. It will do the same thing but faster because you aren't creating and dropping temp tables that serve no purpose.

CREATE procedure [dbo].[CR_Report]
    @Department varchar(60)  = Null,
    @System varchar(30) = Null,
    @Fromdate date = Null,
    @Todate date = Null
AS

IF @System is Null 
    and @Fromdate is Null 
    and @Todate is Null

    SELECT * FROM [SUAF].[dbo].[Config_Request] where Admin_Department = @Department

IF @Department is Null 
    and @Fromdate is Null 
    and @Todate is Null

    SELECT * FROM [SUAF].[dbo].[Config_Request] where System = @System

IF @Department is NULL 
    and @System is NULL

    SELECT * FROM [SUAF].[dbo].[Config_Request] where convert(date, App_Date, 103) Between @Fromdate and @Todate

IF  @Department is not Null 
    and @System is not Null 
    and @Fromdate is not Null 
    and @Todate is not Null

    SELECT * FROM [SUAF].[dbo].[Config_Request] where convert(date, App_Date, 103) Between @Fromdate and @Todate and Admin_Department = @Department and System = @System

answered Jun 20, 2016 at 14:03

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

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

2 Comments

SR1092 Over a year ago

I don't know if some parameter I gave in my procedure was wrong or what! But this totally did the trick :) Thank you so much for this! I though temp tables were faster ways to retrieve data from the database, but guess I was wrong.. :) Appreciate your assistance.

Sean Lange Over a year ago

Temp tables can be quick but effectively you were making a copy of the data to only select it and then drop the temp table. Glad this helped.

Collectives™ on Stack Overflow

SQL Server Procedure not returning output in PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related