Home Dashboard Directory Help
Search

FOR EACH Row in Table / View by mravikiran


Status: 

Active


4
0
Sign in
to vote
Type: Suggestion
ID: 624533
Opened: 11/25/2010 1:55:25 AM
Access Restriction: Public
Primary Feedback Item: 299231
0
Workaround(s)
view

Description

Quite a lot of times, while writing stored procedures, we have to loop thru a set of rows in a (temp) table with a column specifically for "flag" reasons. We use a WHILE loop for this (and some times cursors).

It would be best if we can have a datatype ROW for this purpose. For example:

DECLARE @MyRow ROW
FOR [EACH] @MyRow IN #Employees
BEGIN
    PRINT @MyRow.FirstName
    PRINT @MyRow.LastName
    PRINT CONVERT(VARCHAR, @MyRow.Age)
END

Also, a dynamic table:

FOR @MyRow IN (select .... join.... join.... where....) MyTable
BEGIN
...
...
...
END

Note: The Keyword "EACH" may be optional.
Details
Sign in to post a comment.
Posted by Microsoft on 12/5/2010 at 10:07 PM
Hi Ravi,
Thanks for your feedback. We are already tracking the request for row value constructor:

http://connect.microsoft.com/SQLServer/feedback/details/299231/add-support-for-ansi-standard-row-value-constructors

So I have resolved yours as duplicate of that request. Implementing that will allow us to extend T-SQL to support additional statements like for each row, update set clause, simplify cursor code and others. We will consider it for a future version of SQL Server.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.