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

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 的回复: