Post reply

Stored Procedure Wont Create - Invalid column name

  • August 9, 2012 at 1:56 pm

    #262544

    Hi All,

    I'm not sure why this isn't working when it works in other Stored Procedures I have made and they run accurately.

    I'm using 2008r2 Enterprise.

    After opening the SP I add a new field to a table called CapProjGLDetail - which is already existing in the database - then I want to update that field using the data in the table.

    ALTER TABLE CapProjGLDetail

    ADD concatKeyDetail nvarchar(255);

    UPDATE CapProjGLDetail

    SET concatKeyDetail = "GL_DETAIL_BSIS-BUKRS" + "GL_DETAIL_BSIS-GJAHR" + "GL_DETAIL_BSIS-BELNR";

    This is all wrapped in the SP. It worked before in other SPs I made but now I always get

    Msg 207, Level 16, State 1, Procedure CapProjFinal_sp, Line 13

    Invalid column name 'concatKeyDetail'

    It's driving me nuts! What am I now knowing about SPs in 2008r2?

    EDIT: Sorry, the error comes on the UPDATE statement.

    Thanks All!

    D

  • August 9, 2012 at 2:02 pm

    #1524084

    you cannot ALTER a table and then also use the column in the same group of commands ; the column has to be added, and then a GO statmenet called, or use dynamic sql to add the column first.

    so ALTER... GO... UPDATE

    or switch to dynamic SQL.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 9, 2012 at 2:18 pm

    #1524096

    Why would you want to add a column in a stored procedure?

    That won't be reusable.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 9, 2012 at 2:22 pm

    #1524102

    I need to make a key on this table, then it gets used to join with another table, then I drop the keys.

    I don't create the table nor have access to have it make the key when the data gets loaded, so I have to make the keys, use them, then discard them for the next time the table is updated.

  • August 9, 2012 at 2:25 pm

    #1524109

    wait, your adding a column, updaiting it to a string value, and then dropping the same column?

    we can certainly help streamline THAT process.

    you can probably use a CTE or something to generate the column equivilent without actually altering the DDL of the column. there's no reason to alter the table inside a procedure.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 9, 2012 at 2:47 pm

    #1524129

    Thanks! CTE did it!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply