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