Dynamic Update Query Problem - 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)

Dynamic Update Query Problem

Author

Topic

prashantdighe
Starting Member

21 Posts

Posted - 2012-05-07 : 01:25:01

Hello Friends,
I m confused did i do correct or not???

set @query1 ='update #temp set ['+@col1+']='+@a+' where id='+CAST(@COUNT as varchar)

EXEC @query1

giving error like

Msg 2812, Level 16, State 62, Procedure Test1, Line 64
Could not find stored procedure ''.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-07 : 01:30:27

[code]
EXEC (@query1)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

prashantdighe
Starting Member

21 Posts

Posted - 2012-05-07 : 01:36:58

Thank u khtan,

I tried,

set @query1 ='update #temp set ['+@col1+']='+@a+' where id=' +CAST(@COUNT as varchar)
exec sp_executesql @query1

because,

In @col1 the value of date(e.g.12/03/2012) as column name ,@a having value 'A' as string and @COUNT has value of id means 1,2,3

Now problem when I do above, the value of @a which is 'A' set as column name and give following error...

Msg 207, Level 16, State 1, Line 1

Invalid column name 'A'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-07 : 01:45:59

if @a is string you need to enclosed it in quote in your dynamic sql


set @query1 ='update #temp set ['+@col1+']='''+@a+''' where id=' +CAST(@COUNT as varchar)

KH
[spoiler]Time is always against us[/spoiler]

prashantdighe
Starting Member

21 Posts

Posted - 2012-05-07 : 01:55:49

Wow Thank u khtan its done and working fine Thank you again for your fast and accuarate reply!!!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources