Temp tables and Dynamic query - 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)

Temp tables and Dynamic query

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-05-23 : 08:10:20

i am trying to do :


declare @SqlDynamic nvarchar(2000)=''
drop table #tblMonthlyReports;
Create Table #tblMonthlyReports (
	id int IDENTITY(1,1) NOT NULL,
	Cut varchar(5)
  );
   set @SqlDynamic='ALTER  TABLE #tblMonthlyReports  ADD  Month200904 bigint'
   exec @SqlDynamic

and i get

quote:
Could not find stored procedure 'ALTER TABLE #tblMonthlyReports ADD Month200904 bigint'.

any idea why?
Thanks
Peleg

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-05-23 : 08:33:08

solved it with
EXECUTE sp_executesql @SqlDynamic

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-24 : 10:17:44

drop table #tblMonthlyReports;
Create Table #tblMonthlyReports (
id int IDENTITY(1,1) NOT NULL,
Cut varchar(5)
)
GO
ALTER TABLE #tblMonthlyReports ADD Month200904 bigint

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-05-25 : 06:58:23

Madhivanan, the point is to make a dynamic query, and the line i wrote is only one of few that will change later.

Peleg

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-25 : 07:40:52

Hi pelegk2,

the syntax error you got in the first post is because you have to call EXEC (@sqlDynamic) -- Note the Parenthesis. Or you can call sp_executeSql stored proc also.

I think you should read this:
http://www.sommarskog.se/dynamic_sql.html

But -- be careful with dynamic sql -- it's often a poor choice.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-05-25 : 12:31:42

ok thanks for the info!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources