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.
| Author |
Topic |
|
nirene
Yak Posting Veteran
98 Posts |
 Posted - 2012-05-25 : 02:03:10
|
| Hai SQL Teamers,I have a dynamic query which is executed and the result I am not able to view what am I doing wrong.Select Column_name,Data_type,Character_maximum_length into #AAAXfrom information_schema.columns WHERE TABLE_NAME = 'VSInc' and (Column_name like '%Incentive%' or Column_name like '%Alw%' or Column_name like '%Others%')Declare @intFlag Int,@Cnt Int,@Str varchar(2000)Set @Str='Select Company,Branch,Mon,Yr,Ecode,Ename,'Select @Str=@Str+'Isnull('+Column_name+',0) as ' + Column_name +',' from #AAAXSet @Str=Left(@Str,Len(@Str)-1) + ' Into #BBBX from VSInc'Exec(@Str)Select * from #BBBX not able to execute this statementGO |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-25 : 02:14:53
|
the problem is when you use exec(), it is executed in a separate context. #BBBX is created there and when exec() ended, the temp table is also auto dropped.Don't use INSERT INTO #BBBX FROM .... Create table #BBBX first and use the following in your dynamic SQLINSERT INTO #bbbx SELECT Company, Branch ...
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
nirene
Yak Posting Veteran
98 Posts |
Posted - 2012-05-25 : 02:35:42
|
| Hai Khtan,I tried a sample this way its not working.Declare @X Varchar(1000)Set @X='Create Table #X (XY varchar(20))'Exec(@X)Set @X='Insert Into #X Select ''XXXXXX'''Exec(@X)Select * from #XNirene |
|
|
|
nirene
Yak Posting Veteran
98 Posts |
Posted - 2012-05-25 : 07:21:08
|
| Hai All,I figured it out.And thanks Khtan for your helpDeclare @X Varchar(1000)Create Table #X (XY varchar(20))Set @X='Insert Into #X Select ''XXXXXX'''Exec(@X)Select * from #XNirene |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Dynamic Query