alter statement error

See more:

SQL-Server

i have an alter statement inside of my procedure
with some update statements.
but if run my stored proc it is not creating.
there is no syntax errors but it is showing column is invalid

see below code any one can help me.

IF EXISTS (SELECT *
           FROM   SYS.OBJECTS
           WHERE  OBJECT_ID = Object_id(N'[dbo].[SP_ChemoAdminCodes]')
                  AND TYPE IN ( N'P', N'PC' ))
  DROP PROCEDURE [dbo].[SP_ChemoAdminCodes]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************************************************************

***********************************************************************************************************************/
CREATE PROCEDURE [dbo].[SP_ChemoAdminCodes]
AS
  BEGIN
    
  
if object_id('dbo.tmpHCDTableResultClaims_chemo') is not null
	drop table dbo.tmpHCDTableResultClaims_chemo
if object_id('dbo.tmpHCDAllResults_chemo') is not null
	drop table dbo.tmpHCDAllResults_chemo
if object_id('dbo.tmpHCDTableCL_chemo') is not null
	drop table dbo.tmpHCDTableCL_chemo
if object_id('dbo.tmpHCDResults_chemo') is not null
	drop table dbo.tmpHCDResults_chemo
if object_id('dbo.tmpHCDResultsChemo_chemo') is not null
	drop table dbo.tmpHCDResultsChemo_chemo
 
	Create Table dbo.tmpHCDTableResultClaims_chemo
	(ResultUID varchar(255), 
	 ClaimSummaryUID varchar(255),
	 MemberID varchar(255),
	 FirstDateOfService datetime,
	 ProviderTaxID varchar(255))


---- below  alter statement gives error like 

/*
Msg 207, Level 16, State 1, Procedure SP_ChemoAdminCodes, Line 59
Invalid column name 'SelectionResultStatusID'
*/

	Alter table client29.dbo.tmpHCDResultsCompoundDrugs 
	add  SelectionResultStatusID int
/* ikkada varaki end pedite alter avvtondi*/

--alter table tmpHCDResultsCompoundDrugs drop column SelectionResultStatusID 
--select * from dbo.tmpHCDResultsCompoundDrugs 


	Update A
	Set SelectionResultStatusID = R.SelectionResultStatusID
	from dbo.tmpHCDResultsCompoundDrugs A, Test_AssistedSelection.dbo.SelectionResult R
	where A.resultUID = R.ResultUID
	and R.SelectionRequestID = (select MAX(selectionrequestID)
						  from Test_AssistedSelection.dbo.SelectionRequest
						  where QueryString Like 'HCDCompoundedPain%')
	Alter table dbo.tmpHCDResultsHerceptinFrequency
	Add SelectionresultStatusID int

	Update A
	Set SelectionresultStatusID = R.SelectionResultStatusID
	from dbo.tmpHCDResultsHerceptinFrequency A, Test_AssistedSelection.dbo.SelectionResult R
	where A.resultUID = R.ResultUID
	and R.SelectionRequestID = (select MAX(selectionrequestID)
						  from Test_AssistedSelection.dbo.SelectionRequest
						  where QueryString Like 'HCDHerceptinFreq%')
						  
    Alter table tmpHCDResultsHerceptinVial
	Add SelectionresultStatusID int
	
	Update A
	Set SelectionresultStatusID = R.SelectionResultStatusID
	from dbo.tmpHCDResultsHerceptinVial A, Test_AssistedSelection.dbo.SelectionResult R
	where A.resultUID = R.ResultUID
	and R.SelectionRequestID = (select MAX(selectionrequestID)
						  from Test_AssistedSelection.dbo.SelectionRequest
						  where QueryString Like 'HCDHerceptinFull%')
						  						  
--	Alter table tmpHCDResultsInfDups
--	Add SelectionresultStatusID int
--select * from  dbo.tmpHCDResultsInfDups
--	Update A
--	Set SelectionresultStatusID = R.SelectionResultStatusID
--	from tmpHCDResultsInfDups A, Test_AssistedSelection.dbo.SelectionResult R
--	where A.resultUID = R.ResultUID
--	and R.SelectionRequestID = (select MAX(selectionrequestID)
--						  from Test_AssistedSelection.dbo.SelectionRequest
--						  where QueryString Like 'HCDInfusionDups%')
	Create Table dbo.tmpHCDAllResults_chemo
	(ResultUID varchar(255))
	Insert Into dbo.tmpHCDAllResults_chemo
	Select ResultUID From dbo.tmpHCDResultsCompoundDrugs 
	Where SelectionResultStatusID Not in (2,4)
	Union All 
	Select ResultUID From tmpHCDResultsHerceptinFrequency 
	Where SelectionResultStatusID Not in (2,4)
	Union All 
	Select ResultUID From tmpHCDResultsHerceptinVial
	Where SelectionResultStatusID Not in (2,4)
	--Union All 
	--Select ResultUID From tmpHCDResultsInfDups 
	--Where SelectionResultStatusID Not in (2,4)
	Insert into dbo.tmpHCDTableResultClaims_chemo
	Select Distinct L.ClaimLineUID as ResultUID,
	L.ClaimSummaryUID, S.MemberID, L.FirstDateOFService, S.ProviderTaxID
	From dbo.ClaimLine L
	Inner Join dbo.ClaimSummary S
	on S.ClaimSummaryUID = L.ClaimSummaryUID
	Where ClaimLineUID = Some(Select ResultUID From dbo.tmpHCDAllResults_chemo)
	Create Table dbo.tmpHCDTableCL_chemo (ClaimLineUID varchar(255),
								MemberID varchar(255),
								CPTCode varchar(50),
								ExtraCPTCode varchar(50),
								PaidAmount money,
								FirstDateOFService datetime,
								ProviderTaxID varchar(255))

	INSERT INTO dbo.tmpHCDTableCL_chemo
	SELECT DISTINCT Target.ClaimLineUID, 
					Target.MemberID,
					Target.CPTCode,
					Target.PaidAmount,
					Target.FirstDateOfService,
					Target.ProviderTaxID
	FROM KeepHCDTarget as Target
	Where Target.PlaceOfService = '11'
	  AND EXISTS (SELECT 1 
				  FROM ITWork.dbo.keepHCDCPTAdminCodes A
				  WHERE Target.CPTCode      = A.CPT)
	  And EXISTS (SELECT 1
				   FROM dbo.tmpHCDTableResultClaims_chemo R
				   Where (R.ClaimSummaryUID = Target.ClaimSummaryUID
					  or (R.MemberID = Target.MemberID
					 and R.ProviderTaxID = Target.ProviderTaxID
					 and R.FirstDateOFService = Target.FirstdateOfService)))

	SELECT DISTINCT ClaimLine.ClaimLineUID AS ResultUID
	into dbo.tmpHCDResults_chemo
	From dbo.tmpHCDTableCL_chemo as ClaimLine
	SELECT *
	into dbo.tmpHCDResultsChemo_chemo
	From dbo.tmpHCDResults_chemo
	  
if object_id('dbo.tmpHCDTableResultClaims_chemo') is not null
	drop table dbo.tmpHCDTableResultClaims_chemo
if object_id('dbo.tmpHCDAllResults_chemo') is not null
	drop table dbo.tmpHCDAllResults_chemo
if object_id('dbo.tmpHCDTableCL_chemo') is not null
	drop table dbo.tmpHCDTableCL_chemo
if object_id('dbo.tmpHCDResults_chemo') is not null
	drop table dbo.tmpHCDResults_chemo
	
	
	END