Stored Procedure "Caching"

• LightVader

  Hall of Fame

  Points: 3810

  More actions

  March 3, 2021 at 10:04 pm

  #3853063

  Jeffrey Williams wrote:

  Jonathan AC Roberts wrote:

  Jeffrey Williams wrote:

  Jonathan AC Roberts wrote:

  I see, I think an inner join would do the same job.

  I can't see how your stored procedure is reusing a temporary table from another call. It looks like a bug in SQL Server.

  An INNER join will only work if both parameters *always* have the same number of items.  If @pricePoint = '1' and @priceComponent = 'a|b|c' you want 3 rows returned.  Joining on the ItemNumber - which is the return value from the split would only join the first value and the other price components would not be included.

  The procedure raises an error and returns without doing anything if any nulls are found in the full join.

  SELECT @nullNodeID = COUNT(*) FROM #Nodes2Process WHERE nodeID IS NULL
  SELECT @nullFigID = COUNT(*) FROM #Nodes2Process WHERE figureID IS NULL
  
  IF @nullNodeID > 0 
  BEGIN
  RAISERROR ('Invalid Number of Node IDs Provided.  The number of node IDs must match the number of figure IDs.  Report processing halted.', 11, 0, 'x')
  DROP TABLE #Nodes2Process
  RETURN
  END
  
  IF @nullFigID > 0 
  BEGIN
  RAISERROR ('Invalid Number of Figure IDs Provided.  The number of figure IDs must match the number of node IDs.  Report processing halted.', 11, 0, 'x')
  DROP TABLE #Nodes2Process
  RETURN
  END

  So clearly there aren't supposed to a different number of rows in each parameter.

  That is why I asked if that was the case - based on the original statement the @pricePoint parameter is being passed, but the @priceComponent parameter is not being passed.  If they were passing the same number of items in each list - then using an INNER JOIN eliminates the need to check for NULL in either column.

  There could also be a problem with passing 'blank' values - if @pricePoint = '1||2' and @priceComponent = 'a|b|c' the checks will be passed but there is now a blank NodeID for row 2.

  Maybe a better check would be to validate the number of delimiters at the beginning:

       IF (len(@pricePoint) - len(replace(@pricePoint, '|', '')) <> len(@priceComponent) - len(replace(@priceComponent, '|', '')))
    BEGIN
          RAISERROR('Invalid Number of parameters.  The number of node IDs must match the number of figure IDs.  Report processing halted.', 11, 0) WITH nowait;
          RETURN;
      END
  

  And then - check if any of the passed in values are blank. 

  That's my fault for simplifying the original description of the problem too much.

  I like your check for the number of nodeIDs vs figureIDs.  That seems a bit more elegant then what we've got now.

  ---

  
  The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.