SQL error:137

[WordTechinc]

I am getting error: 137 Must declare the variable '@RNCodes'.

Please advide me.

CREATE PROCEDURE [dbo].[INRV2_Insert_gdtResultDT_RN_bkup_20090915_TEST]
(
@RNCodeXMLString ntext,
@XMLString ntext,
@JobNo char(5),
@UiD UNIQUEIDENTIFIER
)
AS
SET NOCOUNT ON

DECLARE @DocId int
DECLARE @BARVALUE INT


EXEC sp_XML_preparedocument @DocId OUTPUT, @XMLString


DELETE FROM gdtResultDT WHERE JobNo=@JobNo

DELETE FROM tbl_ProgressBar_Save where UiD= @UiD


------------------------------------------- RN indicator parm -- 20090915 --------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @RNDocId int
EXEC sp_XML_preparedocument @RNDocId OUTPUT, @RNCodeXMLString

DECLARE @RNCodes table
(
upZip_Code char(10),
upRNind numeric(18,0),
upNonRNind numeric(18,0),
upRNNonRNind numeric(18,0)
)

INSERT INTO @RNcodes
(upZip_Code, upRNind, upNonRNind, upRNNonRNind)
SELECT
Zip_Code, RNind, NonRNind, RNNonRNind

FROM OpenXML(@RNDocId,'Root/RNCodeTab',2)
WITH
(
Zip_Code char(10),
RNind numeric(18,0),
NonRNind numeric(18,0),
RNNonRNind numeric(18,0)
)
------------------------- end RN indicator --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

INSERT INTO gdtResultDT
(
JobNo, [zip code], [prospective count], [responder count], [reduction], [percresponse],ProsDentCount,
RespDentCount,ProsNonDentCount,RespNonDentCount,ProsRNCount,RespRNCount,RNreduction,RNpercresponse,ProsNonRNCount,RespNonRNCount,NonRNreduction,NonRNpercresponse,
RNind,NonRNind,RNNonRNind
)

SELECT @JobNo,
zip_code,
prospective_count,
responder_count,
reduction,
percresponse,
denproscount,
denrespcount,
nondenproscount,
nondenrespcount,
-- RN spilit added suk 20090814 --
RN_prospective_count,
RN_responder_count,
RNreduction,
RNpercresponse,

NonRN_prospective_count,
NonRN_responder_count,
NonRNreduction,
NonRNpercresponse,

RNind,
NonRNind,
RNNonRNind


FROM OpenXML(@DocId,'Root/gdtResultDT',2)
WITH
(
zip_code char(10),
prospective_count numeric(18, 0),
responder_count numeric(18, 0),
reduction real,
percresponse real ,
denproscount numeric(18, 0),
denrespcount numeric(18, 0),
nondenproscount numeric(18, 0),
nondenrespcount numeric(18, 0),

RN_prospective_count numeric(18, 0),
RN_responder_count numeric(18, 0),
RNreduction real,
RNpercresponse real,

NonRN_prospective_count numeric(18, 0),
NonRN_responder_count numeric(18, 0),
NonRNreduction real,
NonRNpercresponse real,

RNind numeric(18,0),
NonRNind numeric(18,0),
RNNonRNind numeric(18,0)

)myXML


--- update RN indicator - gdtResultDt by Jobno& Zip Code -- Error : 137 here ----------------------------------------------------------------------------------------------

UPDATE gdtResultDT set gdtResultDT.RNind = @RNCodes.upRNind

FROM @RNCodes INNER JOIN gdtResultDT ON
gdtResultDT.zip_code = @RNCodes.upZip_Code
WHERE gdtResultDT.JobNo = @JobNo

-- end update RN indicator - gdtResultDT --------------------------------------------------------------------------------------------------------------------------
GO

 

[gmmastros]

You have this:

        DECLARE @RNCodes table
        (
            upZip_Code char(10),
            upRNind numeric(18,0),
            upNonRNind numeric(18,0),
            upRNNonRNind numeric(18,0)
        )

And then later this:

UPDATE gdtResultDT set gdtResultDT.RNind = @RNCodes.upRNind

FROM @RNCodes INNER JOIN gdtResultDT ON
    gdtResultDT.zip_code = @RNCodes.upZip_Code
WHERE gdtResultDT.JobNo = @JobNo

@RNCodes is a table variable. When you use a table variable in a query that involves multiple tables (like your update query), you need to provide an alias for the table variable. You also need to use the alias everywhere else (for that query). Like this:

UPDATE gdtResultDT set gdtResultDT.RNind = [!]R[/!].upRNind

FROM @RNCodes [!]As R[/!] INNER JOIN gdtResultDT ON
    gdtResultDT.zip_code = [!]R[/!].upZip_Code
WHERE gdtResultDT.JobNo = @JobNo

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jebenson]

Do you have any Visual Basic .NET code that is getting this error? If not, you may want to post this in forum183.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!