@KuWei = 'in ('YCCQ','ECCQ')'@KuWei1 = 'like '%%''@sql='select MMITNO, MMITDS,BaoZhuan,KuWei,KuWeiName,MTTRQT,TiaoJian into #t
from (
SELECT MMITNO, MMITDS, MTRGDT, MTBANO, MTTRQT, MSSLDS, dbo.GetExchange(MTTTID, MTTRTP, MTTTYP, @type) AS TiaoJian,
dbo.Lot2PackageName(MTBANO) AS BaoZhuan, dbo.FixWhs(MSWHSL, MSSLDS, 0) AS KuWei, dbo.FixWhs(MSWHSL, MSSLDS, 1) AS KuWeiName
FROM dbo.WAREHOUSE1
WHERE (LEFT(MMITNO, 1) = '3')) as tb
where (MTRGDT between @StartD and @EndD) AND MMITDS LIKE N+'%'+@MMITDS+'%'+ AND KuWei'if tt=0
@sql=@sql+@KuWei
elseif tt=1
@sql=@sql+@KuWei1exec(@sql)
如何把以上语句改正确
from (
SELECT MMITNO, MMITDS, MTRGDT, MTBANO, MTTRQT, MSSLDS, dbo.GetExchange(MTTTID, MTTRTP, MTTTYP, @type) AS TiaoJian,
dbo.Lot2PackageName(MTBANO) AS BaoZhuan, dbo.FixWhs(MSWHSL, MSSLDS, 0) AS KuWei, dbo.FixWhs(MSWHSL, MSSLDS, 1) AS KuWeiName
FROM dbo.WAREHOUSE1
WHERE (LEFT(MMITNO, 1) = '3')) as tb
where (MTRGDT between @StartD and @EndD) AND MMITDS LIKE N+'%'+@MMITDS+'%'+ AND KuWei'if tt=0
@sql=@sql+@KuWei
elseif tt=1
@sql=@sql+@KuWei1exec(@sql)
如何把以上语句改正确
from (
SELECT MMITNO, MMITDS, MTRGDT, MTBANO, MTTRQT, MSSLDS, dbo.GetExchange(MTTTID, MTTRTP, MTTTYP, '+@type+') AS TiaoJian,
dbo.Lot2PackageName(MTBANO) AS BaoZhuan, dbo.FixWhs(MSWHSL, MSSLDS, 0) AS KuWei, dbo.FixWhs(MSWHSL, MSSLDS, 1) AS KuWeiName
FROM dbo.WAREHOUSE1
WHERE (LEFT(MMITNO, 1) = ''3'')) as tb
where (MTRGDT between '+@StartD+' and '+@EndD+') AND MMITDS LIKE N+''%'''+@MMITDS+'''%'' AND KuWei'if tt=0
set @sql=@sql+@KuWei
else if tt=1
set @sql=@sql+@KuWei1exec(@sql)
--楼主写半拉,没法测试,试试吧
@KuWei = 'in (''YCCQ',''ECCQ')'@KuWei1 = 'like ''%%'''@sql='select MMITNO, MMITDS,BaoZhuan,KuWei,KuWeiName,MTTRQT,TiaoJian into #t
from (
SELECT MMITNO, MMITDS, MTRGDT, MTBANO, MTTRQT, MSSLDS, dbo.GetExchange(MTTTID, MTTRTP, MTTTYP, @type) AS TiaoJian,
dbo.Lot2PackageName(MTBANO) AS BaoZhuan, dbo.FixWhs(MSWHSL, MSSLDS, 0) AS KuWei, dbo.FixWhs(MSWHSL, MSSLDS, 1) AS KuWeiName
FROM dbo.WAREHOUSE1
WHERE (LEFT(MMITNO, 1) = ''3'')) as tb
where (MTRGDT between @StartD and @EndD) AND MMITDS LIKE N+''%''+@MMITDS+''%''+ AND KuWei'if tt=0
@sql=@sql+@KuWei
elseif tt=1
@sql=@sql+@KuWei1exec(@sql)
from (
SELECT MMITNO, MMITDS, MTRGDT, MTBANO, MTTRQT, MSSLDS, dbo.GetExchange(MTTTID, MTTRTP, MTTTYP, '+@type+') AS TiaoJian,
dbo.Lot2PackageName(MTBANO) AS BaoZhuan, dbo.FixWhs(MSWHSL, MSSLDS, 0) AS KuWei, dbo.FixWhs(MSWHSL, MSSLDS, 1) AS KuWeiName
FROM dbo.WAREHOUSE1
WHERE (LEFT(MMITNO, 1) = ''3'')) as tb
where (MTRGDT between '+@StartD+' and '+@EndD+') AND MMITDS LIKE N+''%'''+@MMITDS+'''%'' AND KuWei'if tt=0
set @sql=@sql+@KuWei
else if tt=1
set @sql=@sql+@KuWei1exec(@sql)
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Stockyard_All]
-- Add the parameters for the stored procedure here
(@StartD nvarchar(8),@EndD nvarchar(8),@MMITDS nvarchar(60),@KuWei nvarchar(100),@packYN int,@type int)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
Declare @sql nvarchar(4000)IF NOT (SELECT Object_id( 'Tempdb..#t')) IS NULL
drop table #t
set @sql='select MMITNO, MMITDS,BaoZhuan,KuWei,KuWeiName,MTTRQT,TiaoJian into #t
from (
SELECT MMITNO, MMITDS, MTRGDT, MTBANO, MTTRQT, MSSLDS, dbo.GetExchange(MTTTID, MTTRTP, MTTTYP, '+@type+') AS TiaoJian,
dbo.Lot2PackageName(MTBANO) AS BaoZhuan, dbo.FixWhs(MSWHSL, MSSLDS, 0) AS KuWei, dbo.FixWhs(MSWHSL, MSSLDS, 1) AS KuWeiName
FROM dbo.WAREHOUSE1
WHERE (LEFT(MMITNO, 1) = ''3'')) as tb
where (MTRGDT between '+@StartD+' and '+@EndD+') AND MMITDS LIKE N+''%'''+@MMITDS+'''%'' AND KuWei'+@KuWei
-----含包装
if (@packYN = 0)
begin
if (@type=0)
begin
Set @sql = 'Select MMITNO, MMITDS,BaoZhuan,KuWei,KuWeiName'
Select @sql = @sql + ',sum(case TiaoJian when N'''+exchange+''' then MTTRQT else 0 end) ['+exchange+']' from (select distinct exchange from TiaoJian where exchange is not null) as #t
Select @sql = @sql+' from #t group by MMITNO,MMITDS,BaoZhuan,KuWei,KuWeiName'
end
else if (@type=1)
begin
Set @sql = 'Select MMITNO, MMITDS,BaoZhuan,KuWei,KuWeiName'
Select @sql = @sql + ',sum(case TiaoJian when N'''+Fixexchange+''' then MTTRQT else 0 end) ['+Fixexchange+']' from (select distinct Fixexchange from TiaoJian where Fixexchange is not null) as #t
Select @sql = @sql+' from #t group by MMITNO,MMITDS,BaoZhuan,KuWei,KuWeiName'
end
end
----不含包装
else if (@packYN = 1)
begin
if (@type=0)
begin
Set @sql = 'Select MMITNO, MMITDS,KuWei,KuWeiName'
Select @sql = @sql + ',sum(case TiaoJian when N'''+exchange+''' then MTTRQT else 0 end) ['+exchange+']' from (select distinct exchange from TiaoJian where exchange is not null) as #t
Select @sql = @sql+' from #t group by MMITNO,MMITDS, KuWei,KuWeiName'
end
else if (@type=1)
begin
Set @sql = 'Select MMITNO, MMITDS,KuWei,KuWeiName'
Select @sql = @sql + ',sum(case TiaoJian when N'''+Fixexchange+''' then MTTRQT else 0 end) ['+Fixexchange+']' from (select distinct Fixexchange from TiaoJian where Fixexchange is not null) as #t
Select @sql = @sql+' from #t group by MMITNO,MMITDS, KuWei,KuWeiName'
end
end
exec (@sql)
END
完整的代码,不知道怎么改,现在就是把参数@KuWei改成是动态的,因为@KuWei有2种情况,1种是like‘%%’全部显示,第2种是in('YCCQ','ECCQ'.......)选择显示,请教高手啊。