Updating variable table

I'm creating a basic stored procedure in SQL and I'm running into some issues when using a variable table.

Currently I have it setup like this:

                DECLARE @Results AS TABLE(Id int null, Username nvarchar(max) null, TeamId nvarchar(max) null,
                        Info nvarchar(max) null, HNumber int null, Notes nvarchar(max) null,
                        OutcomeStatus nvarchar(max), ReviewDate date, OutcomeDate date null, InvestigationResoluionId nvarchar(max) null,
                        Discriminator nvarchar(max) null, CloseReasonId nvarchar(max) null, ReferralTypeId nvarchar(max) null)


                        INSERT INTO @Results (Id, UserName, HitsNumber, Notes, OutcomeStatus, ReviewDate, OutcomeDate, TeamId, Discriminator, CloseReasonId, ReferralTypeId)

                SELECT Id, UserName, HNumber, Notes, OutcomeStatus, ReviewDate, OutcomeDate, TeamId, Discriminator, CloseReasonId, ReferralTypeId

                FROM Trackings
                      UPDATE @Results

                        SET OutcomeStatus =
                        
                        (CASE
                        WHEN OutcomeStatus = 0 THEN 'None'
                        WHEN OutcomeStatus = 1 THEN 'Referred'
                        WHEN OutcomeStatus = 2 THEN 'Closed'
                        END)

                        UPDATE @Results

                        Set TeamId = Teams.Description
                        FROM Teams
                        JOIN Trackings
                        ON Teams.Id = MedicTrackings.TeamId


The first part of the query using the case statement work correctly, however when using UPDATE, I'm not getting the correct results back for the team.  It seems to just alternate the first two values in the results. So I get 4 rows with alternating with the first two values, even though the Id's don't match up with this.
delmarvamonkeyAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes you can but that's is mostly a JOIN between the two tables (Teams and @Results).
There's no difference between them so just chose your flavor ;)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your 2nd UPDATE is quite strange:
UPDATE @Results
Set TeamId = Teams.Description
FROM Teams --> You need to join this table with @Results
      JOIN Trackings ON Teams.Id = MedicTrackings.TeamId --> Where MedicTrackings comes from? There is no alias with that name
0
 
delmarvamonkeyAuthor Commented:
Sorry I meant:

 ON Teams.Id = Trackings.TeamId
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You still need to join Teams or Trackings table with @Results.
0
 
delmarvamonkeyAuthor Commented:
Is this correct syntax?

                        UPDATE @Results

                        Set TeamName = Teams.Description
                        FROM Teams
                        JOIN Trackings
                        ON Teams.Id = Trackings.TeamId
                        WHERE Trackings.TeamId = [@Results].TeamId
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, that may work. Alternatively you can also use the following:
UPDATE R
Set R.TeamName = Teams.Description
FROM @Results R
	JOIN Teams ON R.TeamId = Teams.Id 
	JOIN Trackings ON Teams.Id = Trackings.TeamId

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Even I'm not sure why do you'll need Trackings table.
0
 
delmarvamonkeyAuthor Commented:
The Teams table is a lookup table.

Could I just do a simple

WHERE Teams.Id = [@Results].TeamId?
0
 
delmarvamonkeyAuthor Commented:
Ok, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.