通过输入年月查询数据并将其插入到临时表中

czlinmm 2015-07-23 03:23:25
条件是输入年月和乡镇 乡镇可为空


我是这么写的

create proc j
@年月 datetime,
@乡镇编码 varchar(100)
as
create table #777 (AdministrativeName varchar(100),ClientNO varchar(100) ,AdministrativeNO varchar(100) ,Address varchar(100),SubcategoryNO varchar(100),MeterBookNO varchar(100),MeterCaliberID int, PriorCopy numeric ,CurrentCopy numeric , CurrentDay datetime ,PriorDay datetime ,CustomStateNO varchar(100),BuildDay datetime ,Name varchar(100) );
insert into #777 (BuildDay) select BuildDay from Client where Client.BuildDay=@年月
update #777 set ClientNO=Client.ClientNO,AdministrativeNO=Client.AdministrativeNO ,Address=Client.Address ,SubcategoryNO=Client.SubCategoryNO,MeterBookNO=Client.MeterBookNO,PriorCopy=Client.PriorCopy,CurrentCopy=Client.CurrentCopy,CurrentDay=Client.CurrentDay,PriorDay=Client.PriorDay,CustomStateNO=Client.CustomStateNO,Name=Client.Name from #777, Client where #777.BuildDay=Client.BuildDay
update #777 set AdministrativeName = b.Name
from #777 a ,Administrative b
where a.AdministrativeNO = b.NO
update #777 set MeterCaliberID = MeterCaliber.Caliber
from #777 right join Client on #777.ClientNO=Client.ClientNO right join Meter on Client.SealNO=Meter.SealNO right join MeterCaliber on Meter.MeterCaliberID=MeterCaliber.MeterCaliberID
if @乡镇编码 is null
select * from #777
else
select * from #777 where AdministrativeName=@乡镇编码
go



exec j 201008 ,null




但这样始终无法显示数据 就是只显示一张空的临时表


求解决
...全文
118 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
shoppo0505 2015-07-23

create proc j
    @年月 datetime,
    @乡镇编码 varchar(100) = null
as
begin
    select
        a.BuildDay,
        a.ClientNO,
        b.Name as AdministrativeNO,
        a.[Address],
        a.SubcategoryNO,
        a.MeterBookNO,
        d.Caliber as MeterCaliberID,
        a.PriorCopy,
        a.CurrentCopy,
        a.CurrentDay,
        a.PriorDay,
        a.CustomStateNO,
        a.Name
    from Client a
    join Administrative b on a.AdministrativeNO = b.[NO]
    join Meter c on a.SealNO=c.SealNO 
    join MeterCaliber d on c.MeterCaliberID=d.MeterCaliberID
    where
		DatePart(year,a.BuildDay)= DatePart(year,@年月)
		AND DatePart(MONTH,a.BuildDay)= DatePart(MONTH,@年月)
		AND((@乡镇编码 is null) or (b.Name=@乡镇编码))
		
end
exec j '2011-08-08', null
回复
Tiger_Zhao 2015-07-23
@年月 条件是个日期范围!
CREATE PROC J
@年月 datetime,
@乡镇编码 varchar(100)
AS
BEGIN
create table #777 (/* 这个不变 */);

DECLARE @dt1 datetime
DECLARE @dt2 datetime
DECLARE @sql varchar(max)

-- 求出本月初和下月初
SET @dt1 = CONVERT(datetime,
CONVERT(varchar(7),@年月,120)+'-01',
120)
SET @dt1 = DATEADD(month,1,@dt2)

-- 借用#3的代码做成动态SQL
SET @sql = 'INSERT INTO #777
SELECT b.name AS AdministrativeName,
a.clientno,
a.administrativeno,
a.[address],
a.subcategoryno,
a.meterbookno,
d.caliber AS metercaliberid,
a.priorcopy,
a.currentcopy,
a.currentday,
a.priorday,
a.customstateno,
a.buildday,
a.[name]
FROM client a
JOIN administrative b
ON a.administrativeno = b.[no]
JOIN meter c
ON a.sealno = c.sealno
JOIN metercaliber d
ON c.metercaliberid = d.metercaliberid
WHERE a.buildday >= '''+CONVERT(varchar(10),dt1,120)+'''
AND a.buildday < '''+CONVERT(varchar(10),dt2,120)+''''

IF @乡镇编码 IS NOT NULL
SET @sql = @sql+'
AND b.name = '''+@乡镇编码+''''

EXEC(@sql)

SELECT * FROM #777
END
GO
回复
czlinmm 2015-07-23
引用 5 楼 Landa_Ran 的回复:
你先查查这个语句
select
        a.BuildDay,
        a.ClientNO,
        b.Name as AdministrativeNO,
        a.[Address],
        a.SubcategoryNO,
        a.MeterBookNO,
        d.Caliber as MeterCaliberID,
        a.PriorCopy,
        a.CurrentCopy,
        a.CurrentDay,
        a.PriorDay,
        a.CustomStateNO,
        a.Name
    from Client a
    join Administrative b on a.AdministrativeNO = b.[NO]
    join Meter c on a.SealNO=c.SealNO 
    join MeterCaliber d on c.MeterCaliberID=d.MeterCaliberID
    where a.BuildDay='2011-08-08 00:00:00.000'
这个就可以了 但是查询的时候是根据输入年月查询当月的
回复
许晨旭 2015-07-23
你先查查这个语句
select
        a.BuildDay,
        a.ClientNO,
        b.Name as AdministrativeNO,
        a.[Address],
        a.SubcategoryNO,
        a.MeterBookNO,
        d.Caliber as MeterCaliberID,
        a.PriorCopy,
        a.CurrentCopy,
        a.CurrentDay,
        a.PriorDay,
        a.CustomStateNO,
        a.Name
    from Client a
    join Administrative b on a.AdministrativeNO = b.[NO]
    join Meter c on a.SealNO=c.SealNO 
    join MeterCaliber d on c.MeterCaliberID=d.MeterCaliberID
    where a.BuildDay='2011-08-08 00:00:00.000'
回复
czlinmm 2015-07-23
引用 3 楼 Landa_Ran 的回复:
create proc j
	@年月 datetime,
	@乡镇编码 varchar(100)
as
begin
	select
		a.BuildDay,
		a.ClientNO,
		b.Name as AdministrativeNO,
		a.[Address],
		a.SubcategoryNO,
		a.MeterBookNO,
		d.Caliber as MeterCaliberID,
		a.PriorCopy,
		a.CurrentCopy,
		a.CurrentDay,
		a.PriorDay,
		a.CustomStateNO,
		a.Name
	into #777
	from Client a
	join Administrative b on a.AdministrativeNO = b.[NO]
	join Meter c on a.SealNO=c.SealNO 
	join MeterCaliber d on c.MeterCaliberID=d.MeterCaliberID
	where a.BuildDay=@年月

	if  @乡镇编码 is null
		select * from #777
	else 
		select * from #777 where AdministrativeName=@乡镇编码
end
go
不行啊 还是没有数据 顺带说一下 Client里面BuildDay的格式是 2011-08-08 00:00:00.000
回复
许晨旭 2015-07-23
create proc j
	@年月 datetime,
	@乡镇编码 varchar(100)
as
begin
	select
		a.BuildDay,
		a.ClientNO,
		b.Name as AdministrativeNO,
		a.[Address],
		a.SubcategoryNO,
		a.MeterBookNO,
		d.Caliber as MeterCaliberID,
		a.PriorCopy,
		a.CurrentCopy,
		a.CurrentDay,
		a.PriorDay,
		a.CustomStateNO,
		a.Name
	into #777
	from Client a
	join Administrative b on a.AdministrativeNO = b.[NO]
	join Meter c on a.SealNO=c.SealNO 
	join MeterCaliber d on c.MeterCaliberID=d.MeterCaliberID
	where a.BuildDay=@年月

	if  @乡镇编码 is null
		select * from #777
	else 
		select * from #777 where AdministrativeName=@乡镇编码
end
go
回复
czlinmm 2015-07-23
引用 1 楼 yangb0803 的回复:
select BuildDay from Client where Client.BuildDay=@年月 这一句查询出来有无数据?
不给我这么查 要求我声明变量 要是全部一起的执行的话 一样没有数据
回复
道玄希言 2015-07-23
select BuildDay from Client where Client.BuildDay=@年月 这一句查询出来有无数据?
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-07-23 03:23
社区公告
暂无公告