Jeremy Miller Jeremy Miller - 6 months ago 15
SQL Question

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

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?

Answer

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;