精华贴:
http://www.csdn.net/expert/topic/838/838010.xml?temp=.8110926N_Chow的回答:
SQL Server 2K:
Exec sp_serveroption 'YOUR SERVER NAME','Data Access','True'Select * INTO #tmpWho
From OpenQuery([YOUR SERVER NAME],'Exec sp_who')
你的问题可以:
Exec sp_serveroption 'TEST','Data Access','True'
GOSelect * into #t
From OpenQuery([TEST],'exec sp_executeSql N''select * from test..b where id<10''')--这里假设你的@query_nvarchar内容是:N'select * from test..b where id<10'
http://www.csdn.net/expert/topic/838/838010.xml?temp=.8110926N_Chow的回答:
SQL Server 2K:
Exec sp_serveroption 'YOUR SERVER NAME','Data Access','True'Select * INTO #tmpWho
From OpenQuery([YOUR SERVER NAME],'Exec sp_who')
你的问题可以:
Exec sp_serveroption 'TEST','Data Access','True'
GOSelect * into #t
From OpenQuery([TEST],'exec sp_executeSql N''select * from test..b where id<10''')--这里假设你的@query_nvarchar内容是:N'select * from test..b where id<10'
Insert Into #TempTable exec sp_executeSql @query_nvarchar
Select * From #TempTable 这样不行吗?定义#TempTable时,要与SP的返回记录集保持一致.屏蔽掉SP的Print(如果有的话).
这样我当然知道.关键是#tempTable的表结构是根据@query_nvarchar来得,不是固定的呀
DECLARE @ExecStr nvarchar(500)
Select @ExecStr = ' Select * Into #TempTable From YourTable Where .. = ''' + @query_nvarchar + ''''
...
Exec @ExecStr
在@ExecStr范围内,#TempTable不用定义,会形成与@query_nvarchar一样的表结构,在@ExecStr中也可以加入别的语句来实现你的要求.
......
@SQL = 'select identity(int) id0,* into #temp1 from '+@table
exec (@SQL)
select * from #temp1 where id0 >=n and id0<=m
不行呀
提示#temp1不存在
CREATE PROCEDURE split_page
@page int output,
@pageSize int,
@queryFromBF varchar(800),
@queryFromAF varchar(800)
AS
declare @query varchar(1600)
declare @count int
declare @beginCount int
declare @endCount int
declare @pagecount int
set @query = 'select identity(int) split_id,'+@queryFromBF+' into #temp1 from '+@queryFromAF
exec (@query)
select @Count=count(*) from #temp1
set @pageCount=ceiling(@Count/@pageSize)
if(@page>@pageCount) set @page=@pageCount
set @beginCount=(@page-1)*@pageSize+1
set @endCount=@page*@pageSize
select * from #temp1 where split_id between @beginCount and @endCount
GO
......
@SQL = 'select identity(int) id0,* into ##temp'+cast(@@spid as varchar)+' from '+@table
exec (@SQL)
exec ('select * from #temp'+cast(@@spid as varchar)+' where id0 >=n and id0<=m')
declare @table varchar(255),@SQL varchar(4000)
......
@SQL = 'select identity(int) id0,* into ##temp'+cast(@@spid as varchar)+' from '+@table
exec (@SQL)
exec ('select * from ##temp'+cast(@@spid as varchar)+' where id0 >=n and id0<=m')这次的肯定好用。
比方说原identity字段名字叫a1
那么原select into语句要改成:
@SQL = 'select identity(int) id0,a1 * 1 a1,其它字段1,其它字段2 …… into ##temp'+cast(@@spid as varchar)+' from '+@table
你的又一个100分没有了。
因为我不知道哪个字段名称是identity字段。但是我想用这种办法是不可能做到更灵活了你还有更好的其它办法解决我说的这种目的吗?如果有,我再加100分
并将identity属性的字段做*1处理。
传入表名,返回该表所有的字段名.
聪明的你一定知道该怎幺做了吧。create proc up_getTableColumnName
@tableName varchar(256),
@columnName varchar(2000) out
as
set @columnName = ''
select @columnName = @columnName + (case when colstat& 1 = 1 then ','+name+'*1 '+name
else ',' + name end)
from syscolumns where id = object_id('test') order by colid
set @columnName = right(@columnName,len(@columnName)-1)
你真不该遇上了我,你的又一个100分没了。 :p
要知道难倒我是一件很不容易的事,哈哈。
正确的应该是:create proc up_getTableColumnName
@tableName varchar(256),
@columnName varchar(2000) out
as
set @columnName = ''
select @columnName = @columnName + (case when colstat& 1 = 1 then ','+name+'*1 '+name
else ',' + name end)
from syscolumns where id = object_id(@tableName) order by colid
set @columnName = right(@columnName,len(@columnName)-1)