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.
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.
ASKER
Sorry I meant:
ON Teams.Id = Trackings.TeamId
ON Teams.Id = Trackings.TeamId
You still need to join Teams or Trackings table with @Results.
ASKER
Is this correct syntax?
UPDATE @Results
Set TeamName = Teams.Description
FROM Teams
JOIN Trackings
ON Teams.Id = Trackings.TeamId
WHERE Trackings.TeamId = [@Results].TeamId
UPDATE @Results
Set TeamName = Teams.Description
FROM Teams
JOIN Trackings
ON Teams.Id = Trackings.TeamId
WHERE Trackings.TeamId = [@Results].TeamId
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
Even I'm not sure why do you'll need Trackings table.
ASKER
The Teams table is a lookup table.
Could I just do a simple
WHERE Teams.Id = [@Results].TeamId?
Could I just do a simple
WHERE Teams.Id = [@Results].TeamId?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, thanks.
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