用 DECLARE @mytable 声明 table 类型的变量。
解决方案 »
- sql server 删除记录 会自动重建索引么
- 怎么建立全文索引
- 为什么我用查询分析器时用ip连不上,用数据库实例名就可以连上?谢谢!
- 性能问题,我的统计作业要10个小时才能执行完(包括很多步),上次移到新的服务器上时,才4个小时就搞定了,各位指点下为什么。。。
- 怎么才能把减法的到的float保留两位小数
- mdf物理文件如何打开
- 字符串分割的问题?
- 如何用存储过程查询安装Sql Server机器的系统目录(windir,SystemDir,ProgramDir等等)?
- SQL Server 2000 行列转换 大侠请进来帮帮忙..............
- 怎样找出(某个字段)下一行比本行大的所有行?
- 那里设置有了问题?
- SQL SERVER 2000个人版的运行问题,望大家给出建议。
exec('select hylx ,zzfljg INTO '+ @mytable+' FROM FLRDSPB')
GO
@mytable varchar(20)
AS
declare @strSql varchar(8000)
select @strSql='select hylx ,zzfljg INTO ' + @mytable + ' FROM FLRDSPB'
exec(@strSql)
FROM FLRDSPB
GO
新建表名要动态产生的话要使用动态sql
参考联机帮助
AS
select KHJBXXB.hylx as 行业,FLRDSPB.zzfljg as 最终分类结果,SUM(FLRDSPB.jine) as 金额 INTO @mytable FROM FLRDSPB,KHJBXXB
WHERE (KHJBXXB.KHBH=FLRDSPB.KHBH) and (FLRDSPB.bz=人民币) and (SUBSTRING(FLRDSPB.jzr,1,4)=@myyear) and (SUBSTRING(FLRDSPB.jzr,6,2)=@mymonth)
GROUP BY KHJBXXB.HYLX,FLRDSPB.ZZFLJG
ORDER BY KHJBXXB.HYLX,FLRDSPB.ZZFLJG
GO
我的存储过程是上面这样的,就是传进去一个表名,使SELECT生成的记录集放到这个表里面,但是我这样写的话参数传不进去,是什么原因呢,我只是传一个表名
,哪位大侠帮忙看看呀,十万火急呀
AS
Declare @SQL Varchar(4000)
Select @SQL='select KHJBXXB.hylx as 行业,FLRDSPB.zzfljg as 最终分类结果,SUM(FLRDSPB.jine) as 金额 INTO '+@mytable+' FROM FLRDSPB,KHJBXXB
WHERE (KHJBXXB.KHBH=FLRDSPB.KHBH) and (FLRDSPB.bz=人民币) and (SUBSTRING(FLRDSPB.jzr,1,4)='+@myyear+') and (SUBSTRING(FLRDSPB.jzr,6,2)='+@mymonth+')
GROUP BY KHJBXXB.HYLX,FLRDSPB.ZZFLJG
ORDER BY KHJBXXB.HYLX,FLRDSPB.ZZFLJG
Exec(@SQL)
GO
CREATE PROCEDURE dbo.hytjbb @myyear varchar(8),@mymonth varchar(8) , @mytable varchar(20)
AS
Declare @SQL Varchar(4000)
Select @SQL='select KHJBXXB.hylx as 行业,FLRDSPB.zzfljg as 最终分类结果,SUM(FLRDSPB.jine) as 金额 INTO '+@mytable+' FROM FLRDSPB,KHJBXXB
WHERE (KHJBXXB.KHBH=FLRDSPB.KHBH) and (FLRDSPB.bz=人民币) and (SUBSTRING(FLRDSPB.jzr,1,4)='+@myyear+') and (SUBSTRING(FLRDSPB.jzr,6,2)='+@mymonth+')
GROUP BY KHJBXXB.HYLX,FLRDSPB.ZZFLJG
ORDER BY KHJBXXB.HYLX,FLRDSPB.ZZFLJG'
Exec(@SQL)
GO
declare @execStr varchar(8000)
set @execStr='select hylx ,zzfljg INTO '+ @mytable + 'FROM FLRDSPB'
exec(@execStr)
GO--执行时:
exec hytjbb 'table1'
建议你把语句更改成:
CREATE PROCEDURE dbo.hytjbb @mytable varchar(20) AS
select * INTO #39@mytable
FROM 定单01
select * from #39@mytable
GO
你就可以非常清楚的看到,创建的临时表已经是被成功创建了,只是它又被删除了。若你确实要用此句,建议把你的局部临时表更改为全局临时表,即改为如下语句:
CREATE PROCEDURE dbo.hytjbb @mytable varchar(20) AS
select * INTO ##39@mytable
FROM 定单01
GO
你再用语句:
select * from ##39@mytable 查询一下,应该没有问题的。
FROM FLRDSPB
GO或:select @mytable=....
exec('select hylx ,zzfljg INTO #39'+@mytable+' FROM FLRDSPB')
GO
AS
exec('select KHJBXXB.hylx as 行业,FLRDSPB.zzfljg as 最终分类结果,SUM(FLRDSPB.jine) as 金额 INTO '+@mytable+' FROM FLRDSPB,KHJBXXB
WHERE (KHJBXXB.KHBH=FLRDSPB.KHBH) and (FLRDSPB.bz=人民币) and (SUBSTRING(FLRDSPB.jzr,1,4)='+@myyear+') and (SUBSTRING(FLRDSPB.jzr,6,2)='+@mymonth+')
GROUP BY KHJBXXB.HYLX,FLRDSPB.ZZFLJG
ORDER BY KHJBXXB.HYLX,FLRDSPB.ZZFLJG'
GO--調用存儲過程
exec hytjbb '2004','08','mytata'
CREATE PROCEDURE dbo.hytjbb @myyear varchar(8),@mymonth varchar(8) , @mytable varchar(20)
AS
exec('select KHJBXXB.hylx as 行业,FLRDSPB.zzfljg as 最终分类结果,SUM(FLRDSPB.jine) as 金额 INTO '+@mytable+' FROM FLRDSPB,KHJBXXB
WHERE (KHJBXXB.KHBH=FLRDSPB.KHBH) and (FLRDSPB.bz=''人民币'') and (SUBSTRING(FLRDSPB.jzr,1,4)='''+@myyear+') and (SUBSTRING(FLRDSPB.jzr,6,2)='+@mymonth+')
GROUP BY KHJBXXB.HYLX,FLRDSPB.ZZFLJG
ORDER BY KHJBXXB.HYLX,FLRDSPB.ZZFLJG'
GO--調用存儲過程
exec hytjbb '2004','08','mytata'