Update Table Variable in SQL Server and getting Must declare the scalar variable

Question

I CAN change to temp table if need be, but when I am doing an UPDATE on a table variable in sql server, why are I getting this error and how can I fix, should I switch to temp table ?

Must declare the scalar variable "@rpmuserTableVariable".

DECLARE @rpmuserTableVariable TABLE
(
 [usr_id] [varchar](8) NOT NULL,
    [usr_fnm] [varchar](64) NOT NULL,
    [usr_lnm] [varchar](64) NOT NULL,
    [usr_pwd] [varchar](64) NOT NULL,
    [email_id] [varchar](250) NULL,
    [wwid] [varchar](8) NULL,
    [tel] [char](20) NULL,
    [dflt_ste_id] [int] NOT NULL,
    [lst_pwd_chg_dtm] [datetime] NULL,
    [lst_accs_dtm] [datetime] NULL,
    [apprvr_wwid] [varchar](8) NULL,
    [inact_ind] [varchar](1) NOT NULL,
    [cre_usr_id] [varchar](8) NOT NULL,
    [cre_dtm] [datetime] NOT NULL,
    [chg_usr_id] [varchar](8) NULL,
    [chg_dtm] [datetime] NULL,
    [salt] [varchar](20) NULL,
    STATUS [char] (1) NULL 
);

-- All Active Users  
INSERT INTO @rpmuserTableVariable
SELECT *  ,'0'                       
FROM rpm_scrty_rpm_usr WITH(NOLOCK)                                             
WHERE inact_ind = 'N'
-- Internal Users  
UPDATE @rpmuserTableVariable 
SET STATUS = 1 
FROM rpm_scrty_rpm_usr ru WITH(NOLOCK)                      
INNER JOIN rpm_scrty_emp_info ei WITH(NOLOCK)                      
       ON ru.wwid = ei.wwid                    
WHERE ru.inact_ind = 'N'                       
AND ei.inact_ind = 'N'
AND ei.dmn_addr IS NOT NULL   
AND @rpmuserTableVariable.usr_id = ru.usr_id 


select * from @rpmuserTableVariable

Do I need to use a temp table #tempblah or is there a "trick" to doing this?

Also, I CAN do a bulk update right? I do not need to do a WHILE loop do I?

Do you know which line/statement the error is thrown? – Nick Jun 07 '16 at 21:59 — Nick, Jun 07 '16 at 21:59
AND @rpmuserTableVariable.usr_id = ru.usr_id – Jun 07 '16 at 22:03 — , Jun 07 '16 at 22:03
Thanks. I think @GordonLinoff found the solution for you. – Nick Jun 07 '16 at 22:05 — Nick, Jun 07 '16 at 22:05

score 2 · Accepted Answer · answered Jun 07 '16 at 21:59

2

No need. You just need a table alias. Aliases cannot start with @:

UPDATE rtv
    SET STATUS = 1 
FROM @rpmuserTableVariable rtv INNER JOIN
     rpm_scrty_rpm_usr ru WITH(NOLOCK)
     ON rtv.usr_id = ru.usr_id INNER JOIN
     rpm_scrty_emp_info ei WITH(NOLOCK)                      
     ON ru.wwid = ei.wwid                    
WHERE ru.inact_ind = 'N' AND                   
      ei.inact_ind = 'N' AND
      ei.dmn_addr IS NOT NULL;

answered Jun 07 '16 at 21:59

Gordon Linoff

1,242,037
58
646
786

3

Can he just deal with the sql parsing issue instead with just adding brackets around it? `[@rpmuserTableVariable]` ? – Tom Stickel Jun 07 '16 at 22:02
@TomStickel . . . I just tested that in a recent version of SQL Server, and it works. I remembering trying it years ago and it didn't work -- either my memory is off or this functionality changed. In any case, I prefer having the explicit `JOIN` rather than the condition in the `WHERE` clause. Plus, I almost always use abbreviations as table aliases. – Gordon Linoff Jun 07 '16 at 22:15

Update Table Variable in SQL Server and getting Must declare the scalar variable

1 Answers1