July 12, 2011 at 12:18 pm
Hi,
Using DB Mail, I am attempting to audit a reference table.
The query looks through new data and sees if there are an references that don't have a definition in a lookup table.
This works fine if there are 0 or 1 new references, as I am assigning it to the variable and then I just plug that in the email body. The issue is when the result set is >1, it can not assign to a variable.
Does anyone know how to either assign multiple values to a variable, or go about this a better way (keeping in mind that this has to be added to the body of an email later (I tried used a table variable, but that just got me stuck when sending the email))
Thanks!
July 12, 2011 at 12:41 pm
How about using a table variable? 🙂
_________________________________
seth delconte
http://sqlkeys.com
July 12, 2011 at 12:53 pm
Something like this maybe? Adapted to fit your needs of course, but I think this should work.
DECLARE @Message VARCHAR(MAX)
DECLARE @ResultsTable TABLE
(
ID INT IDENTITY PRIMARY KEY,
[Message] VARCHAR(MAX)
)
INSERT INTO @ResultsTable ([Message])
VALUES ('This is a message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is a new message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is a newer message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is the newest message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is an old message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is an older message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is the oldest message')
SET @Message = 'These are the messages in the table'
SELECT @Message = @Message + CHAR(13) + CHAR(10) + [Message]
FROM @ResultsTable
PRINT @Message
July 12, 2011 at 12:55 pm
kramaswamy (7/12/2011)
Something like this maybe? Adapted to fit your needs of course, but I think this should work.
DECLARE @Message VARCHAR(MAX)
DECLARE @ResultsTable TABLE
(
ID INT IDENTITY PRIMARY KEY,
[Message] VARCHAR(MAX)
)
INSERT INTO @ResultsTable ([Message])
VALUES ('This is a message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is a new message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is a newer message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is the newest message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is an old message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is an older message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is the oldest message')
SET @Message = 'These are the messages in the table'
SELECT @Message = @Message + CHAR(13) + CHAR(10) + [Message]
FROM @ResultsTable
PRINT @Message
You're awesome!
July 12, 2011 at 12:59 pm
The only drawback to my solution is that, I *think*, there may be a limit to how much can fit into that @Message variable, even though it is a VARCHAR(MAX) field. Also, you might encounter performance if the variable is being fed in with large amounts of data.
The way this thing is working, it is basically re-binding the variable @Message at every record in the table. Which means when the message starts growing larger, it will exponentially increase in complexity.
If you're dealing with large amounts of data, a better solution would be to use XML to form your message.
July 12, 2011 at 1:21 pm
Incase you're curious about the XML solution, you could try something like
DECLARE @Message VARCHAR(MAX)
DECLARE @ResultsTable TABLE
(
ID INT IDENTITY PRIMARY KEY,
[Message] VARCHAR(MAX)
)
INSERT INTO @ResultsTable ([Message])
VALUES ('This is a message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is a new message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is a newer message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is the newest message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is an old message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is an older message')
INSERT INTO @ResultsTable ([Message])
VALUES ('This is the oldest message')
SELECT TOP 1 @Message =
(
SELECT '' + [Message] + CHAR(10) from @ResultsTable t2 FOR XML PATH('')
)
FROM @ResultsTable t1
PRINT @Message
See this thread for more details. I had this same problem like a year ago or so 😛
http://www.sqlservercentral.com/Forums/Topic841787-338-1.aspx
July 12, 2011 at 1:24 pm
Not working with a lot of data here, but I appreciate the 2nd solution. Always nice to have that in your back pocket 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply