-1

I have a query here that I want to store the photo image and its history. I ran into this "Must Declare the scalar variable" message when I am trying to use the @myTable again. Any ideas for the correct syntax?

DECLARE @myTable Table (ID INT, thumbnail varbinary(MAX))

INSERT INTO dbo.Inspection_photo
                (Hea_ID,
                 RowGuid,
                 Photo,
                 EnterBy,
                 EnterDate)
OUTPUT INSERTED.PK_PHOTO, @photo_thumbnail INTO @myTable(ID, thumbnail)
        VALUES (@hea_id,
                NEWID(),
                @photo,
                @enterby,
                @enterdate)

INSERT INTO dbo.Inspection_Photo_Thumbnail
    Select ID, Thumbnail
    From @myTable

INSERT INTO dbo.Inspection_Photo_Hist
            (PK_Photo,
             RowGuid,
             Hea_ID,
             Photo,
             EnterDate,
             EnterBy,
             ModDate,
             ModBy,
             Type)
    Values (@myTable.ID,
            NEWID(),
            @hea_id,
            @photo,
            @enterdate,
            @enterby,
            @enterdate,
            @enterby,
            'INS')
3

Your problems is this line.

Values (@myTable.ID,

You need to use the table @myTable in a FROM clause. Remove the table valued constructor and do a SELECT instead.

INSERT INTO dbo.Inspection_Photo_Hist
            (PK_Photo,
             RowGuid,
             ...
             Type)
SELECT T.ID,
       NEWID(),
       ...
       'INS'
FROM @myTable AS T;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.