Dynamic Variable Names in SQL

Question

I have a (hopefully) quick SQL question that is driving me nuts and I have not been able to locate an answer anywhere.

I have the following SQL trigger:

DECLARE @ABCOwnerGroup varchar(30)
DECLARE @DEFOwnerGroup varchar(30)
SET @ABCOwnerGroup='GROUP ONE'
SET @DEFOwnerGroup='GROUP TWO'
etc..

--IF OWNERGROUP IS MISSING (Location NOT NULL)
    UPDATE wo
        SET wo.ownergroup='@'+SUBSTRING(wo.location,1,3)+'OwnerGroup'
    FROM dbo.workorder AS wo INNER JOIN inserted AS i ON wo.wonum=i.wonum
    WHERE wo.status<>'COMP'
        AND wo.historyflag=0
        AND wo.istask=0    
        AND wo.ownergroup IS NULL
        AND wo.location IS NOT NULL

For your information, the location codes are like 'ABC-12345' where ABC is essentially the site and 12345 is the building. So the SUBSTRING(wo.location,1,3) pulls the ABC part of the location so that it can fill in @ABCOwnerGroup

The issue is that it is inserting the value '@ABCOwnerGroup' instead of 'GROUP ONE'

Any and all help is greatly appreciated! Hopefully this is a minor error!

Answer 1

You need to express your entire update as a string, and use Exec to execute it.

Answer 2

You'll need to use an EXECUTE to exec dynamic SQL

EXEC('UPDATE wo SET wo.ownergroup=@'+SUBSTRING(wo.location,1,3)+'OwnerGroup FROM dbo.workorder AS wo INNER JOIN inserted AS i ON wo.wonum=i.wonum
WHERE wo.status<>''COMP''
    AND wo.historyflag=0
    AND wo.istask=0    
    AND wo.ownergroup IS NULL
    AND wo.location IS NOT NULL')

Answer 3

Alternatively you can get the same result without dynamic sql with the following:

--IF OWNERGROUP IS MISSING (Location NOT NULL)
    UPDATE wo
        SET wo.ownergroup = CASE SUBSTRING(wo.location, 1, 3)
                                WHEN 'ABC' THEN 'GROUP ONE'
                                WHEN 'DEF' THEN 'GROUP TWO'
                            END
    FROM dbo.workorder AS wo
    INNER JOIN inserted AS i ON wo.wonum = i.wonum
    WHERE wo.status <> 'COMP'
        AND wo.historyflag = 0
        AND wo.istask = 0
        AND wo.ownergroup IS NULL
        AND wo.location IS NOT NULL