SPROC ERROR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SPROC ERROR

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-10-21 : 11:42:17

Hi
i get an error for the following sproc. Any help in rectifying this

Update B
set B.ASC_CHANGES = 2 from ASCG as A
inner join (select * from ASCG where asc_Eff_Start = '04/01/2008') as B
on A.ASC_HCPCS = B.ASC_HCPCS
where A.asc_Eff_Start = '01/01/2008'
and a.asc_base <> b.ASC_BASE
and B.ASC_CHANGES is null
and b.asc_link is null

create procedure sp_ASCratechange
@date1 varchar(10),
@date2 varchar(10)
as
Update B
set B.ASC_CHANGES = 2 from ASCG as A
inner join (select * from ASCG where asc_Eff_Start = @date2) as B
on A.ASC_HCPCS = B.ASC_HCPCS
where A.asc_Eff_Start = @date1
and a.asc_base <> b.ASC_BASE
and B.ASC_CHANGES is null
and b.asc_link is null

execute sp_ASCratechange '01/01/2008', '04/01/2008'

Msg 4421, Level 16, State 1, Procedure sp_ASCratechange, Line 5
Derived table 'B' is not updatable because a column of the derived table is derived or constant.

Sachin.Nand

2937 Posts

Posted - 2010-10-21 : 12:01:56

Maybe this


Update B A
set B.ASC_CHANGES = 2 from ASCG as A
inner join (select * from ASCG where asc_Eff_Start = '04/01/2008') as B
on A.ASC_HCPCS = B.ASC_HCPCS
where A.asc_Eff_Start = '01/01/2008'
and a.asc_base <> b.ASC_BASE
and B.ASC_CHANGES is null
and b.asc_link is null

PBUH

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-10-21 : 14:54:38

i tried your method and it gives me this error
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "B.ASC_CHANGES" could not be bound.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources