May 15, 2009 at 9:02 am
I have a series of INSERTs that take place within a loop structure. Through each iteration of the loop, a key value is extracted from a Cursor. From one of the INSERTs, I need to display an inserted value in the Management Studio Message window. The insert looks like this:
INSERT INTO dbo.ApplicationUser
(PersonID,
StatusCodeID,
UserName,
[Password],
NormalizedPassword,
DateCreated
)
SELECT
pr.PersonID,
1 AS StatusCodeID,
'agent' + CAST(pr.AgentID AS varchar(20)) AS UserName,
CAST('agent' + (CAST(REVERSE(zp.ZipCode) AS varchar(9))) AS varbinary) AS [Password],
CAST('agent' + (CAST(REVERSE(zp.ZipCode) AS varchar(9))) AS varbinary) AS NormalizedPassword,
dbo.udf_GetCurrentDateTime() AS DateCreated
FROM
dbo.Person pr
INNER JOIN dbo.Agent ag
ON pr.AgentID = ag.AgentID
INNER JOIN AddressDetail ad
ON ag.MailingAddressID = ad.AddressID
INNER JOIN ZipCode zp
ON zp.ZipCodeID = ad.ZipCodeID
WHERE pr.PersonID = SCOPE_IDENTITY()
--*****************************************
What I need to do is display the value of [Password] to the message window for each insert. How can I do this?
Thank you for your help!
CSDunn
May 15, 2009 at 9:36 am
Hi
You should have a look to BOL for the INSERT statement and its OUTPUT clause. It can be used to write the data into another (e.g. variable) table.
Greets
Flo
May 15, 2009 at 10:14 am
Though you'd probably be better off getting rid of the cursor altogether.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply