ALTER AND UPDATE together not working....

Hi ,
I have a Stored Procedure like this...
| CREATE PROCEDURE V_test AS
| Select MS.memno, MS.ymdeff, MS.ymdend,MS.Aidcode INTO
STAGE_memspan
| FROM membspan MS INNER JOIN STAGE_members MM
I | on SM.memno = MM.memno
|
| ALTER TABLE STAGE_membspan ADD Aidcode_Description char (72)
NULL
| UPDATE STAGE_membspan
| SET Aidcode_Description = CL.[desc]
II | from SATGE_membspan SM, CODE_LOOKUP CL
| where SM.Aidcode = CL.code and CL.id = 'rp'
After executing this I am getting following error :
Invalid Column name 'Aidcode_Description'
If I execute I part seperately and II part seperately it work fine...
Thanks...!!!!.When the parser is examiningthe statement and validating all the =columnames etc tye ALTER has not yet been done, therefore when it =examines the update the column being referenced does not exist. Run =these as two separate batches and all will be fine. Not really sure why =you want an alter in a stored proc anyway since by definition it can =only be done once, and the major benefit of stored procs comes when they =are run many times.
Mike John
"veena" <vgs@.yahoo.com> wrote in message =news:uMfXdWITDHA.1576@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> > I have a Stored Procedure like this...
> > | CREATE PROCEDURE V_test AS
> | Select MS.memno, MS.ymdeff, MS.ymdend,MS.Aidcode INTO
> STAGE_memspan
> | FROM membspan MS INNER JOIN STAGE_members MM
> I | on SM.memno =3D MM.memno
> |
> | ALTER TABLE STAGE_membspan ADD Aidcode_Description char =(72)
> NULL
> > | UPDATE STAGE_membspan
> | SET Aidcode_Description =3D CL.[desc]
> II | from SATGE_membspan SM, CODE_LOOKUP CL
> | where SM.Aidcode =3D CL.code and CL.id =3D 'rp'
> > > After executing this I am getting following error :
> Invalid Column name 'Aidcode_Description'
> > If I execute I part seperately and II part seperately it work fine...
> > Thanks...!!!!.
> > > >=20|||Thanks Mike,
I came to know the reason and also I am not including ALTER in a stored
procedure...since its a one time execution process... Thanks again...
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:#2zPClITDHA.1912@.tk2msftngp13.phx.gbl...
When the parser is examiningthe statement and validating all the columnames
etc tye ALTER has not yet been done, therefore when it examines the update
the column being referenced does not exist. Run these as two separate
batches and all will be fine. Not really sure why you want an alter in a
stored proc anyway since by definition it can only be done once, and the
major benefit of stored procs comes when they are run many times.
Mike John
"veena" <vgs@.yahoo.com> wrote in message
news:uMfXdWITDHA.1576@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> I have a Stored Procedure like this...
> | CREATE PROCEDURE V_test AS
> | Select MS.memno, MS.ymdeff, MS.ymdend,MS.Aidcode INTO
> STAGE_memspan
> | FROM membspan MS INNER JOIN STAGE_members MM
> I | on SM.memno = MM.memno
> |
> | ALTER TABLE STAGE_membspan ADD Aidcode_Description char (72)
> NULL
> | UPDATE STAGE_membspan
> | SET Aidcode_Description = CL.[desc]
> II | from SATGE_membspan SM, CODE_LOOKUP CL
> | where SM.Aidcode = CL.code and CL.id = 'rp'
>
> After executing this I am getting following error :
> Invalid Column name 'Aidcode_Description'
> If I execute I part seperately and II part seperately it work fine...
> Thanks...!!!!.
>
>