I want to include a mutli-row table in an email message later on in the program.

How can I loop through each row of a table and attach it to the email message?


@msg = 'Table Output
@msg = @msg + @tableRow1
@msg = @msg + @tableRow2
@msg = @msg + @tableRow3

To get all the rows, I'm doing a

SELECT * FROM table_name

There are many rows, and many columsn. I need some sort of loop to add each row to my email message as a string.

Use Cursor if you want to loop the table row by row. You can find the detail information in SQL Server Book Online.

Here is an example.


SET NOCOUNT ON

DECLARE @vendor_id int, @vendor_name nvarchar(50),
@message varchar(80), @product nvarchar(50)

PRINT '-------- Vendor Products Report --------'

DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From Vendor: ' +
@vendor_name

PRINT @message

-- Declare an inner cursor based
-- on vendor_id from the outer cursor.

DECLARE product_cursor CURSOR FOR
SELECT v.Name
FROM Purchasing.ProductVendor pv, Production.Product v
WHERE pv.ProductID = v.ProductID AND
pv.VendorID = @vendor_id-- Variable value from the outer cursor

OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product

IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @message = ' ' + @product
PRINT @message
FETCH NEXT FROM product_cursor INTO @product

END

CLOSE product_cursor
DEALLOCATE product_cursor

-- Get the next vendor.
FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor

Is it just me, or is MYSQL easier to use than MSSQL?

That cursor stuff seems like a lot of work for a simple loop.

I just need something like:

foreach row {
$string = $queryresults_row[1];
}

But it needs to work in MSSQL

try something like this...
DECLARE @msg VARCHAR(max)
SELECT @msg = COALESCE(@msg + ' ', '') + tableRow
FROM table_name
SELECT @msg