How to run a query multiple times with different parameters?

Question

I'm trying to figure out the best way to get a query to run multiple times with different parameters. I've tried putting it as a stored procedure and running it with cursors, but I'm pretty novice at the cursor concept. Here is the query and my first attempt with cursor.

SELECT 
AVG([processingseconds])
FROM [nucor_historical_data].[dbo].[test_Lift_Matrix]
Where ActualGauge between 0 and .21875 and ActualWidth between 0 and 55
and inches between   0 and 120 and MaxLiftWeight between 0 and 10000 and
processingseconds is not null

So the parameters I need to loop through are in the where statement. I have combinations for all these groupings you see in another table.

someone suggested trying this to me earlier from another stack question, so I tested with one parameter but couldn't get it working. Is there a better way to attempt this?

DECLARE @param varchar(200)

-- getting your parameter from the table
DECLARE curs CURSOR LOCAL FAST_FORWARD FOR
SELECT gauge FROM groupings

OPEN curs

FETCH NEXT FROM curs INTO @param

-- executing your stored procedure once for every value of your parameter     
WHILE @@FETCH_STATUS = 0 BEGIN
EXEC group_average @param
FETCH NEXT FROM curs INTO @param
END

CLOSE curs
DEALLOCATE curs

Solution 1

Here is a watered down example of what you are trying to do, that is, run the select statement repeatedly using the values from another table as the inputs. You'll need to adapt to your specifics, I only did a subset of your fields:

DECLARE @UniqueId int
DECLARE @AgMin numeric(10,4)
DECLARE @AgMax numeric(10,4)

DECLARE @tmp TABLE (UniqueId INT, AgMin numeric(10,4), AgMax numeric(10,4))
INSERT @tmp SELECT ID, AGMIN, AGMAX FROM [YOUROTHERTABLEWITHTHESENUMBERS]

  SELECT TOP 1  @UniueId=UniqueId, @AGMin=AGMin, @AGMAX=AgMax FROM @tmp
  WHILE (@@rowcount > 0)
  BEGIN
    SELECT AVG([processingseconds]) FROM test_Lift_Matrix Where ActualGauge between @AGMIN and @AGMAX  (the rest of your conditions...)
    DELETE FROM @tmp WHERE UniqueId=@UniqueId
    SELECT TOP 1  @UniqueId=UniqueId, @AGMin=AGMin, @AGMAX=AgMax FROM @tmp
  END

Solution 2

A stored procedure is the way to go here - passing the parameters as arguments.

Comments

[Tyler Cohen]

@E.JBrennan this is like what I was looking to do. Any idea when I get everything set-up it would return only one row and be null?

[Tyler Cohen]

Thanks, this is where I'm eventually headed. I'll give the article a read