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!

Why a trigger? Possibly some Check-Constraints does the job much more easier. — Ice
– Ice, Commented Oct 22, 2010 at 21:01

ulty4life · Accepted Answer · 2010-10-22 16:40:34Z

4

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

answered Oct 22, 2010 at 16:40

ulty4life

3,0101 gold badge27 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

D.R. Over a year ago

I assume I could also use: "WHEN 'ABC' THEN @ABCOwnerGroup" etc Correct?

ulty4life Over a year ago

Yes, you can use a variable in the THEN part of the case statement. You might also want to consider a calculated column as part of the table definition: msdn.microsoft.com/en-us/library/ms191250.aspx

Matt Fellows · Accepted Answer · 2010-10-22 16:11:40Z

3

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')

answered Oct 22, 2010 at 16:11

Matt Fellows

6,5324 gold badges38 silver badges58 bronze badges

2 Comments

HLGEM Over a year ago

Dynamic SQl in a trigger makes me shudder with horror.

D.R. Over a year ago

It is my understanding that Dynamic SQL does not have access to the INSERTED table. Is this correct? If so, I would have to create parameters for the Dynamic SQL and pass the INSERTED table. This seems like a nightmare!

Klaus Byskov Pedersen · Accepted Answer · 2010-10-22 16:10:12Z

2

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

answered Oct 22, 2010 at 16:10

Klaus Byskov Pedersen

122k31 gold badges192 silver badges222 bronze badges

Collectives™ on Stack Overflow

Dynamic Variable Names in SQL

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related