SQL Server 2008 - Update a temporary table

Posted by user336786 on Stack Overflow See other posts from Stack Overflow or by user336786
Published on 2010-05-13T15:19:09Z Indexed on 2010/05/13 15:24 UTC
Read the original article Hit count: 156

Filed under:

Hello,

I have stored procedure in which I am trying to retrieve the last ticket completed by each user listed in a comma-delimited string of usernames. The user may not have a ticket associated with them, in this case I know that i just need to return null. The two tables that I am working with are defined as follows:

User
----
UserName,
FirstName,
LastName

Ticket
------
ID,
CompletionDateTime,
AssignedTo,
AssignmentDate,
StatusID

TicketStatus
------------
ID,
Comments

I have created a stored procedure in which I am trying to return the last completed ticket for a comma-delimited list of usernames. Each record needs to include the comments associated with it. Currently, I'm trying the following:

CREATE TABLE #Tickets
(
  [UserName] nvarchar(256),
  [FirstName] nvarchar(256),
  [LastName] nvarchar(256),
  [TicketID] int,
  [DateCompleted] datetime,
  [Comments] text
)

-- This variable is actually passed into the procedure
DECLARE @userList NVARCHAR(max)
SET @userList='user1,user2,user2'

-- Obtain the user information for each user
INSERT INTO #Tickets
(
  [UserName],
  [FirstName],
  [LastName]
)
SELECT
  u.[UserName],
  u.[FirstName],
  u.[LastName]
FROM
  User u 
    INNER JOIN dbo.ConvertCsvToTable(@userList) l ON u.UserName=l.item

At this point, I have the username, first and last name for each user passed in. However, I do not know how to actually get the last ticket completed for each of these users.

How do I do this? I believe I should be updating the temp table I have created. At the same time, id do not know how to get just the last record in an update statement.

Thank you!

© Stack Overflow or respective owner

Related posts about sql