i have a problem in if statement

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 2000 Forums

SQL Server Development (2000)

i have a problem in if statement

Author

Topic

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-08-13 : 03:45:52

hi all,
i am trying to update tables based on value of @tablename variable
but i am getting the following error
my query is

if(@tablename='tbl_client')
begin
SELECT @TABLENAME
select * from #tbl_temp
update tbl_client set attrib_value=b.attrib_value,attrib_uom=b.attrib_uom from tbl_client a inner join #tbl_temp b on a.id=b.id
end
if(@tablename='tbl_stdclient')
begin
select * from #tbl_temp
SELECT @TABLENAME
update tbl_stdclient set stdattrib_value=b.stdattrib_value from tbl_stdclient a inner join #tbl_temp b on a.id=b.id
end
if(@tablename='tbl_nmpair')
begin
select * from #tbl_temp
SELECT @TABLENAME
update tbl_nmpair set plntno=b.plntno,batchno=b.batchno from tbl_nmpair a inner join #tbl_temp b on a.id=b.id
end

i am getting the following error

Server: Msg 207, Level 16, State 1, Procedure usp_tlfproc, Line 60
Invalid column name 'stdattrib_value'.
Invalid column name 'plntno'.
Server: Msg 207, Level 16, State 1, Procedure usp_tlfproc, Line 66
Invalid column name 'batchno'.
Server: Msg 207, Level 16, State 1, Procedure usp_tlfproc, Line 66

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-13 : 03:54:20

reference the alias used for your 'update' table instead. i.e.

update a set a.plntno=b.plntno,a.batchno=b.batchno from tbl_nmpair a inner join #tbl_temp b on a.id=b.id

Em

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-08-13 : 05:01:52

hi elancaster,
i have used in that way it is giving the same error

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-13 : 05:02:30

post the query you used

Em

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-08-13 : 05:09:44

hi elancaster
this is the query i used, i made the require replacement in other two queries

update a set a.plntno=b.plntno,a.batchno=b.batchno from tbl_nmpair as a inner join #tbl_temp b on a.id=b.id

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-13 : 05:11:26

what are the columns in your temp table?

Em

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-08-13 : 05:17:00

hi elancaster,

My temptable(#tbl_temp)structure is same as tbl_client when @tablename value is 'tbl_client',
i mean to say that i am generating the structure dynamically based on @tablename value

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-13 : 05:19:37

then it looks as though it's not taken the 'other' column names. show us the code you are using for dynamically creating the temp table.

actually... thinking about it, that means columns will always not exist in 2 out of 3 "if's". try naming your temp table seperately

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources