CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Peter Olson
Peter Olson

Reputation: 142921

Loop through table rows in SQL

I have a stored procedure with a table input. I want to somehow loop through the rows of that table. I'm not sure how you do this sort of thing in SQL.

Here is what I am trying to do, in pseudo-SQL:

CREATE PROCEDURE RearrangePuzzles
    ChangedPuzzles table(
      OldDifficulty nvarchar(50),
      OldIndex int,
      NewDifficulty nvarchar(50),
      NewIndex int
    )
AS
FOREACH Row IN ChangedPuzzles 
 BEGIN
   UPDATE Puzzles
    SET Index = Row.NewIndex,
        Difficulty = Row.NewDifficulty
    WHERE Index = Row.OldIndex AND
          Difficulty = Row.OldDifficulty
 END

This, of course, is not valid SQL. How can I write a query with the desired functionality in SQL?

Upvotes: 0

Views: 5348

Answers (5)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

You could use table-valued parameters (new in SQL Server 2008):

CREATE TYPE dbo.ChangedPuzzles AS TABLE
(
      OldDifficulty NVARCHAR(50) NOT NULL,
      OldIndex INT NOT NULL,
      NewDifficulty NVARCHAR(50) NOT NULL,
      NewIndex INT NOT NULL,
      PRIMARY KEY(OldIndex, OldDifficulty)
);
GO

CREATE PROCEDURE dbo.RearrangePuzzles
@Source dbo.ChangedPuzzles READONLY
AS

UPDATE  Puzzles
SET     Index = src.NewIndex,
    Difficulty = src.NewDifficulty
FROM    Puzzles p
INNER JOIN @Source src ON p.Index = src.OldIndex
AND p.Difficulty = src.OldDifficulty;
GO

--Test
DECLARE @p dbo.ChangedPuzzles;
INSERT @p VALUES (10,1,11,11), (20,2,22,12);

EXECUTE dbo.RearrangePuzzles @p;

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

I think it is usually better to take a set-based approach inside of SQL instead of a procedural, line-by-line solution. I believe a JOIN would work in your situation:

UPDATE p
SET
    Index = cp.NewIndex,
    Difficulty = cp.NewDifficulty
FROM
    Puzzles p JOIN
    ChangedPuzzles cp ON cp.OldIndex = p.Index AND cp.OldDifficulty = p.Difficulty

Upvotes: 4

Dan J
Dan J

Reputation: 16708

You can do that via an "Update-From" query:

UPDATE a
SET a.Index = b.NewIndex,
    a.Difficulty = b.NewDifficulty
FROM Puzzles a
JOIN ChangedPuzzles b ON a.Index = b.OldIndex AND a.Difficulty = b.OldDifficulty

Upvotes: 1

James Johnson
James Johnson

Reputation: 46047

I might be missing something, but doesn't this accomplish that?:

UPDATE  Puzzles
SET     Puzzles.[Index] = ChangesPuzzles.NewIndex, 
        Puzzles.Difficulty = ChangedPuzzles.NewDifficulty 
FROM    ChangedPuzzles
WHERE   Puzzles.[Index] = ChangedPuzzles.OldIndex AND Puzzles.Difficulty = ChangesPuzzles.OldDifficulty

Upvotes: 0

Adam V
Adam V

Reputation: 6356

Can't you just do:

UPDATE Puzzles
SET
  Index = cp.NewIndex,
  Difficulty = cp.NewDifficulty
FROM ChangedPuzzles cp
WHERE Index = cp.OldIndex
AND Difficulty = cp.OldDifficulty

(It's been a couple months since I've gotten into SQL, so apologies if the syntax is off)

Upvotes: 0

Related Questions