Procedure runs differently from MSSMS and PHP PDO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Procedure runs differently from MSSMS and PHP PDO

Author

Topic

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2014-11-06 : 14:14:14

My basic issue is that I have a procedure that has 10 steps, 9 of which are other stored procedures. When I call the main procedure manually (via MS SQL Management Studio) it works correctly. When I issue the same EXEC command and parameter from a PHP script via PDO. it runs the procedure, but only the first 9 steps execute. No errors are reported back to PDO, and there's no errors when running manually. Any thoughts?

In case I'm doing something wacky in my SQL causing this, below are the procedures I'm running:


CREATE PROCEDURE main_procedure (@uid int)
AS 
BEGIN
  DECLARE @NOW DATETIME
  SET @NOW = getDate()

  DELETE FROM metrics WHERE uid = @uid AND timestamp < @NOW
  EXEC update_metric_a @uid, @NOW
  EXEC update_metric_b @uid, @NOW
  EXEC update_metric_c @uid, @NOW
  EXEC update_metric_d @uid, @NOW
  EXEC update_metric_e @uid, @NOW
  EXEC update_metric_f @uid, @NOW
  EXEC update_metric_g @uid, @NOW
  EXEC update_metric_h @uid, @NOW
  EXEC update_metric_i @uid, @NOW
END

This is the template used for each metric. It creates a row in the database and populates each column with averages for that individual for a given month. The other 8 are similar.


CREATE PROCEDURE update_metric_a (@uid int, @NOW datetime)
AS

  DECLARE @date datetime
  DECLARE @metric NUMERIC(15,4)
  DECLARE @metric_name VARCHAR(50)
  DECLARE @year_to_date_value NUMERIC(15,4)

  SET @metric_name = 'Metric A'

  INSERT INTO metric 
    (metric_name, timestamp, uid) 
  VALUES 
    (@metric_name, @NOW, @uid)

  DECLARE monthly_metric CURSOR FOR
  SELECT  
    DATEADD(month, DATEDIFF(month, 0, [date]), 0) [Date],  
    CASE WHEN COUNT(id) > 0
      THEN 1.00 * SUM(score) / COUNT(id)
      ELSE null
      END AS [average]
  FROM metric_outages
  WHERE 
    uid = @uid AND 
    [date] >= DATEADD(month,-12,DATEADD(month, DATEDIFF(month, 0, getdate()), 0) )
  GROUP BY DATEADD(month, DATEDIFF(month, 0, [date]), 0)
  ORDER BY DATEADD(month, DATEDIFF(month, 0, [date]), 

  OPEN monthly_metric
  FETCH NEXT FROM monthly_metric INTO @date, @metric

  WHILE @@FETCH_STATUS = 0
  BEGIN

    IF @date =  DATEADD(month,0, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) 
      UPDATE metric SET month_cur = CAST(@metric AS Varchar(20)) WHERE uid = @uid AND timestamp = @NOW AND metric = @metric_name

    ELSE IF @date =  DATEADD(month,-1,DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) 
      UPDATE metric SET month_1 = CAST(@metric AS Varchar(20)) WHERE uid = @uid AND timestamp = @NOW AND metric = @metric_name

    ... and so on ...
      
    ELSE IF @date =  DATEADD(month,-12,DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) 
      UPDATE metric SET month_12 = CAST(@metric AS Varchar(20)) WHERE uid = @uid AND timestamp = @NOW AND metric = @metric_name
    
    FETCH NEXT FROM monthly_metric INTO @date, @metric

  END

  CLOSE monthly_metric
  DEALLOCATE monthly_metric

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-06 : 15:23:07

I cannot see why the last query would fail to run unless the previous one failed.

For that matter, I cannot see why you are using a cursor for the updates. This will probably underperform and it can be rewritten as a single update statement.

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2014-11-06 : 18:09:11

It's really weird. It only fails when called by the PDO driver. If I re-order the procedure calls in main_procedure, the last one is usually the one that doesn't run (though a few times I believe that re-ordering didn't make a difference which I'm guessing could be due to SQL Server internal caching??)

When run in SQL Server Mamagement Studio it runs perfectly every time.

I used the cursor because I had to insert a grouping of data (by month) into a different column (month_cur for the current month, month_1 for the previous month, and so on, with month_12 being 12 months ago). Perhaps I should rethink that, though it shouldn't affect the original problem. The data now contains Jan 2013 - Present data, but I wanted to pre-cache a year's worth of data based on the "Current Date". I think at one point I was using a user supplied date rather then current date as well, so I could possibly re-think this, but it does work. and quickly.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-07 : 08:52:15

Try modifying those called procedures to write a row to a tracking table with a datetime stamp and guid. then you can examine that table to see what ran and what didn't

also, fwiw, you don't need a cursor to do what you want. You can easily compose a single query to do the same thing and get rid of the cursor. As a bonus, it will run many times faster (possibly by an order of magnitude or two)

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2014-11-07 : 10:29:40

I know which one isn't running because one row is entirely missing (The first stage of the procs creates a line with the user id, metric name and leaves blanks in the 13 month columns. This first line doesn't run, but it's not from any one particular procedure. Changing the order of the procedures changes which one doesn't run. Usually it's the last one.

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2014-11-10 : 15:19:03

So I believe the issue has been resolved. I added " SET NOCOUNT ON " to the procedure and it appears to be working properly.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-10 : 15:33:45

That could impact how the calling application processes the results but shouldn't impact the processing of those SP calls. Still, glad you got it working!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources