一个存储过程(32分)

月满C楼 · 2008-08-02

有点长，但很简单，打印出来的SQL能够执行，但就在存储过程内不能执行 CREATE procedure up_ylpf(   @jhrq1 varchar(200),   @jhrq2 varchar(200),   @nkxh varchar(200),   @htbh varchar(200),   @khmc varchar(200) ) as begin     declare       @sql varchar(4000)       set @sql='               select                    a.[ID],                    b.TechnicsId,--to right join                    khmc,                    htbh,                    nkxh,                    gylm,/*钴盐料名*/                    slz,/*深料重*/                    blz,/*白料重*/                    qlz,/*透料重*/                    tlz into #jhbf /*浅料重*/               from jhbf a ,bomTechnicsMaster b               where [dbo].GetModel(a.nkxh)=b.model               and [dbo].GetColor(a.nkxh)=b.color               and jhrq>=''' + @jhrq1 + ''' and jhrq<''' +@jhrq2 + ''''     if @nkxh<>''       set @sql=@sql + ' and a.nkxh like ''' + @nkxh + '%'''         if @htbh<>''       set @sql=@sql + ' and a.htbh like ''' + @htbh + '%'''         if @khmc<>''       set @sql=@sql + ' and a.khmc like ''%' + @khmc + '%'''                   set @sql=@sql + ' order by nkxh'         /*cope with TechItem_ls*/     set @sql=@sql + char(13) + char(13)     set @sql=@sql +     ' select       a.[ID], /*to join #jhbf*/       [PKNO]=1, /*to make the order 深白透浅*/       b.TechnicsId, /*right join bomPaint*/       b.MasterId, /*to join #jhbf in report*/       b.Technics,       a.slz as lz into #TechItem     from #jhbf a,bomTechnicsItem b     where a.TechnicsId=b.MasterId       and ltrim(rtrim(b.Technics))=''深'''             set @sql=@sql + char(13) + char(13)     set @sql=@sql +     ' insert into #TechItem     select       a.[ID], /*to join #jhbf*/       [PKNO]=2, /*to make the order 深白透浅*/       b.TechnicsId, /*right join bomPaint*/       b.MasterId, /*to join #jhbf in report*/       b.Technics,       a.blz as lz     from #jhbf a,bomTechnicsItem b     where a.TechnicsId=b.MasterId       and ltrim(rtrim(b.Technics))=''白'''         set @sql=@sql + char(13) + char(13)     set @sql=@sql +     ' insert into #TechItem     select       a.[ID], /*to join #jhbf*/       [PKNO]=3, /*to make the order 深白透浅*/       b.TechnicsId, /*right join bomPaint*/       b.MasterId, /*to join #jhbf in report*/       b.Technics,       a.tlz as lz       from #jhbf a,bomTechnicsItem b     where a.TechnicsId=b.MasterId       and ltrim(rtrim(b.Technics))=''透'''               set @sql=@sql + char(13) + char(13)     set @sql=@sql +         ' insert into #TechItem     select       a.[ID], /*to join #jhbf*/       [PKNO]=4, /*to make the order 深白透浅*/       b.TechnicsId, /*right join bomPaint*/       b.MasterId, /*to join jhbf_ls in report*/       b.Technics,       a.qlz as lz     from #jhbf a,bomTechnicsItem b     where a.TechnicsId=b.MasterId       and ltrim(rtrim(b.Technics))=''浅'''                /*cope with bomPaint_ls*/     set @sql=@sql + char(13) + char(13)     set @sql=@sql +         ' select       a.[ID], /*to join #TechItem*/       [ID2]=1, /* to make order*/       b.TechnicsId, /*to join #TechItem*/       ''深'' as colorname,       b.DyeName,       a.lz,       b.Amount,       isnull(a.lz,0) * isnull(b.Amount,0) * 10 as weight into #bomPaint     from #TechItem a,bomPaint b     where a.TechnicsId=b.TechnicsId       and ltrim(rtrim(a.Technics))=''深'''           set @sql=@sql + char(13) + char(13)     set @sql=@sql +       ' insert into #bomPaint     select       a.[ID],       [ID2]=2,       b.TechnicsId,       ''白'' as colorname,       b.DyeName,       a.lz,       b.Amount,       isnull(a.lz,0) * isnull(b.Amount,0) * 10 as weight     from #TechItem a,bomPaint b     where a.TechnicsId=b.TechnicsId       and ltrim(rtrim(a.Technics))=''白'''               set @sql=@sql + char(13) + char(13)     set @sql=@sql +         ' insert into #bomPaint         select       a.[ID],       [ID]=3,       b.TechnicsId,       ''透'' as colorname,       b.DyeName,       a.lz,       b.Amount,       isnull(a.lz,0) * isnull(b.Amount,0) * 10 as weight     from #TechItem a,bomPaint b     where a.TechnicsId=b.TechnicsId       and ltrim(rtrim(a.Technics))=''透'''           set @sql=@sql + char(13) + char(13)     set @sql=@sql +         ' insert into  #bomPaint     select     a.[ID],     [ID]=4,     b.TechnicsId,     ''浅'' as colorname,     b.DyeName,     a.lz,     b.Amount,     isnull(a.lz,0) * isnull(b.Amount,0) * 10 as weight   from #TechItem a,bomPaint b   where a.TechnicsId=b.TechnicsId     and ltrim(rtrim(a.Technics))=''浅'''     set @sql=@sql +char(13) + char(13)   set @sql=@sql +   '     select * from #jhbf     select distinct * from #TechItem     select distinct * from #bomPaint     drop table #jhbf     drop table  #TechItem    drop table #bomPaint   '    print @sql                    exec @sql       end GO //测试 exec up_ylpf '2008-7-1','2008-8-1','','','' //打印出来的SQL,复制到查询分析器能够执行，并返回结果集               select                    a.[ID],                    b.TechnicsId,--to right join                    khmc,                    htbh,                    nkxh,                    gylm,/*钴盐料名*/                    slz,/*深料重*/                    blz,/*白料重*/                    qlz,/*透料重*/                    tlz into #jhbf /*浅料重*/               from jhbf a ,bomTechnicsMaster b               where [dbo].GetModel(a.nkxh)=b.model               and [dbo].GetColor(a.nkxh)=b.color               and jhrq>='2008-7-1' and jhrq<'2008-8-1' order by nkxh  select       a.[ID], /*to join #jhbf*/       [PKNO]=1, /*to make the order 深白透浅*/       b.TechnicsId, /*right join bomPaint*/       b.MasterId, /*to join #jhbf in report*/       b.Technics,       a.slz as lz into #TechItem     from #jhbf a,bomTechnicsItem b     where a.TechnicsId=b.MasterId       and ltrim(rtrim(b.Technics))='深'  insert into #TechItem     select       a.[ID], /*to join #jhbf*/       [PKNO]=2, /*to make the order 深白透浅*/       b.TechnicsId, /*right join bomPaint*/       b.MasterId, /*to join #jhbf in report*/       b.Technics,       a.blz as lz     from #jhbf a,bomTechnicsItem b     where a.TechnicsId=b.MasterId       and ltrim(rtrim(b.Technics))='白'  insert into #TechItem     select       a.[ID], /*to join #jhbf*/       [PKNO]=3, /*to make the order 深白透浅*/       b.TechnicsId, /*right join bomPaint*/       b.MasterId, /*to join #jhbf in report*/       b.Technics,       a.tlz as lz       from #jhbf a,bomTechnicsItem b     where a.TechnicsId=b.MasterId       and ltrim(rtrim(b.Technics))='透'  insert into #TechItem     select       a.[ID], /*to join #jhbf*/       [PKNO]=4, /*to make the order 深白透浅*/       b.TechnicsId, /*right join bomPaint*/       b.MasterId, /*to join jhbf_ls in report*/       b.Technics,       a.qlz as lz     from #jhbf a,bomTechnicsItem b     where a.TechnicsId=b.MasterId       and ltrim(rtrim(b.Technics))='浅'  select       a.[ID], /*to join #TechItem*/       [ID2]=1, /* to make order*/       b.TechnicsId, /*to join #TechItem*/       '深' as colorname,       b.DyeName,       a.lz,       b.Amount,       isnull(a.lz,0) * isnull(b.Amount,0) * 10 as weight into #bomPaint     from #TechItem a,bomPaint b     where a.TechnicsId=b.TechnicsId       and ltrim(rtrim(a.Technics))='深'  insert into #bomPaint     select       a.[ID],       [ID2]=2,       b.TechnicsId,       '白' as colorname,       b.DyeName,       a.lz,       b.Amount,       isnull(a.lz,0) * isnull(b.Amount,0) * 10 as weight     from #TechItem a,bomPaint b     where a.TechnicsId=b.TechnicsId       and ltrim(rtrim(a.Technics))='白'  insert into #bomPaint         select       a.[ID],       [ID]=3,       b.TechnicsId,       '透' as colorname,       b.DyeName,       a.lz,       b.Amount,       isnull(a.lz,0) * isnull(b.Amount,0) * 10 as weight     from #TechItem a,bomPaint b     where a.TechnicsId=b.TechnicsId       and ltrim(rtrim(a.Technics))='透'  insert into  #bomPaint     select     a.[ID],     [ID]=4,     b.TechnicsId,     '浅' as colorname,     b.DyeName,     a.lz,     b.Amount,     isnull(a.lz,0) * isnull(b.Amount,0) * 10 as weight   from #TechItem a,bomPaint b   where a.TechnicsId=b.TechnicsId     and ltrim(rtrim(a.Technics))='浅'     select * from #jhbf     select distinct * from #TechItem     select distinct * from #bomPaint     drop table #jhbf     drop table  #TechItem    drop table #bomPaint

一个存储过程(32分)

月满C楼

Unregistered / Unconfirmed

Similar threads