How to do ForEach on user defined table type in SQL Server stored procedure?

Question

XX PROCEDURE [dbo].[XXX]
    @X dbo.IntType readonly
AS
BEGIN
    SET NOCOUNT ON;
    // how can I foreach(@X) here and do process individually?
END

IntType is a user-defined table type

CREATE TYPE [dbo].[IntType] AS TABLE(
    [T] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [T] ASC
)

I need to use this in SQL Azure, please advice.

Answer 1

Why not use a cursor ???

I have to disagree with many of the other answers you'll find here on StackOverflow. Generally you'll see that people have all sorts of bad things to say about cursors .. and they are right when we talk about traditional tables .. only problem is that your question is about a table variable that you use inside a stored procedure.

Your first decision point should always be to see if you can do a set-based operation rather than an iteration (row-by-row processing). Databases are optimized for the former. The answer I give here is for those who have decided that there's no way they can use a set-based approach and the target of iteration is a table variable.

Your table variable is just like a Collection in a programming language. It is a private in-memory structure. There's absolutely no problem in iterating over that in a ForEach style when you are inside the stored procedure. If your scenario truly requires row-by-row processing then a cursor is certainly ok in your case. I really fail to see why not.

Let's go through an example based on your scenario. First we define a table type:

CREATE TYPE [IntListType] AS TABLE
   (   [T] INT  );
GO

Then we define a stored procedure that uses this table as its input:

CREATE PROCEDURE [myTest]
 (
       @IntListInput IntListType READONLY
 )
 AS
 BEGIN
    SET NOCOUNT ON;

    DECLARE @myInt INT;
    DECLARE intListCursor CURSOR LOCAL FAST_FORWARD
    FOR
    SELECT [T]
    FROM @IntListInput;

    OPEN intListCursor;

    -- Initial fetch attempt
    FETCH NEXT FROM intListCursor INTO @myInt;

    WHILE @@FETCH_STATUS = 0
    BEGIN
       -- Here we do some kind of action that requires us to 
       -- process the table variable row-by-row. This example simply
       -- uses a PRINT statement as that action (not a very good
       -- example).
       PRINT 'Int var is : ' + CONVERT(VARCHAR(max),@myInt);

       -- Attempt to fetch next row from cursor
       FETCH NEXT FROM intListCursor INTO @myInt;
    END;

    CLOSE intListCursor;
    DEALLOCATE intListCursor;
 END;
 GO

So, yes, I use a cursor for the iteration.

Note that I use the keywords LOCAL and FAST_FORWARD just to make it very clear (explicit) to the optimizer that I do not intend to update my cursor and I will scroll forward only and I will only access it from within the procedure.

I tested it like this:

DECLARE @IntList IntListType;

-- Put some random data into our list
INSERT INTO @IntList VALUES (33);
INSERT INTO @IntList VALUES (777);
INSERT INTO @IntList VALUES (845);
INSERT INTO @IntList VALUES (71);


EXEC myTest @IntList;
GO

Answer 2

Cursors are the SQL equivalent of ForEach,

But cursors are often a sign of bad SQL: they violate the usual set-based thinking that SQL is built on and optimized for.

Search on SQL cursor or SQL Cursor Azure for many examples, tutorials and optimization notes.

But it can't be said enough: avoid cursors: they are often the crutch for programmers from other languages in SQL and they are often slow and hard to maintain.

Answer 3

You can do something similar to this:

CREATE PROCEDURE [dbo].[testSet]
AS

BEGIN
    SET NOCOUNT ON;

    DECLARE @NumberofIntType            int,
            @RowCount                   int

    -- get the number of items
    SET @NumberofIntType = (SELECT  count(*)
                            FROM dbo.IntType)

    SET @RowCount = 0           -- set the first row to 0

    -- loop through the records 
    -- loop until the rowcount = number of records in your table
    WHILE @RowCount <= @NumberofIntType
        BEGIN
            -- do your process here

            SET @RowCount = @RowCount + 1
        END
END