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ãoConnect With a Mentor MSSQL 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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
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
All Courses

From novice to tech pro — start learning today.