Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 51
  • Last Modified:

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.
0
delmarvamonkey
Asked:
delmarvamonkey
  • 5
  • 4
1 Solution
 
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You still need to join Teams or Trackings table with @Results.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
 
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
 
delmarvamonkeyAuthor Commented:
Ok, thanks.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now