SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE PROC GetLastDealingDate
AS IF ( EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##a' ))
DROP TABLE ##aDECLARE @sTableName varchar(15) select top 1 name
into ##a
from sysobjects
where substring(name, 1, 7) = 'xxxxxxx'
order by name desc select @sTableName = name from ##a select * from @sTableNameGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
解决方案 »
- 大家给看下这样的语句执行百万数据效率怎么样了,谢谢了,要是慢的话能不能优化了!
- Microsoft SQL Server\90\Setup Bootstrap\LOG\Files 下面的日志文件可不可以删除?
- 向高手请教:怎样做到修改表的字段名,而不修改程序?
- SQL SERVER 无法正常启动
- 求助
- 邹建老大,帮忙解决一个数据库进程占cpu高的问题?
- sqlserver高手请进
- 急!!如何删除用ALTER TABLE 语句的 DEFAULT 创建的具有默认值的字段
- 用SQL SERVER的bcp时出现"不能使用 OUTPUT 选项"错误
- 统计问题
- 能帮助看看这几条语句怎么写吗?
- 在做搜索时遇到的问题
--表名为变量时,要使用动态SQL
CREATE PROC GetLastDealingDate
AS IF ( EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##a' ))
DROP TABLE ##aDECLARE @sTableName varchar(15) select top 1 name
into ##a
from sysobjects
where substring(name, 1, 7) = 'xxxxxxx'
order by name desc select @sTableName = name from ##a exec('select * from '+@sTableName)GO
这是定义一个字段符,怎么能在select * from @sTableName 这里当表用呢?
改为
select @stablename
AS
begin
DECLARE @sTableName varchar(15)
select @sTableName=name from sysobjects
where type='u' and substring(name,1,7)='tb'
order by name desc
exec('select * from '+@sTableName)
endexec GetLastDealingDate1 5
5 6
4 7不用临时表,用了你什么时候删除?
GO
SET ANSI_NULLS ON
GO
CREATE PROC GetLastDealingDate
AS
IF ( EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##a' ))
DROP TABLE ##a
DECLARE @sTableName varchar(15)
select top 1 name into ##a
from sysobjects
where substring(name, 1, 7) = 'xxxxxxx'
order by name desc
select @sTableName = name from ##a
declare @sql varchar(8000)
set @sql ='select * from '+ @sTableName
exec(@sql)try
GO
SET ANSI_NULLS ON
GO
CREATE PROC GetLastDealingDate
AS
IF ( EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##a' ))
DROP TABLE ##a
DECLARE @sTableName varchar(15)
select top 1 name into ##a
from sysobjects
where substring(name, 1, 7) = 'xxxxxxx'
order by name desc
select @sTableName = name from ##a
declare @sql varchar(8000)
set @sql ='select * from '+ @sTableName
exec(@sql)try~