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

Martin Milan · Accepted Answer · 2014-03-06 22:29:05Z

5

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

answered Mar 6, 2014 at 22:29

Martin Milan

6,4002 gold badges36 silver badges44 bronze badges

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

Comments

E.J. Brennan · Accepted Answer · 2014-03-06 22:45:23Z

2

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

edited Mar 6, 2014 at 22:45

answered Mar 6, 2014 at 22:37

E.J. Brennan

46.9k8 gold badges94 silver badges120 bronze badges

1 Comment

Tyler Cohen Over a year ago

@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?

Trenton McKinney · Accepted Answer · 2024-03-19 22:57:23Z

0

I think what you're trying to do is a execute a dynamic query (because of the changing where clause) having it behaviour like a static query.

I suppose your query is executed upon criteria coming from client application.

If this is the case try to a parametrized dinamic query string executed through sp_executesql.

This is a very effective technique. See more following:

https://www.sommarskog.se/dyn-search.html

edited Mar 19, 2024 at 22:57

Trenton McKinney

63.2k41 gold badges170 silver badges213 bronze badges

answered Mar 9, 2014 at 9:25

umbio

113 bronze badges

1 Comment

Tyler Cohen Over a year ago

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

Collectives™ on Stack Overflow

How to run a query multiple times with different parameters?

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related