以下语句为交叉表查询,正常通过ALTER PROCEDURE [dbo].[p_p_JiaGongRiBao]
@DyStartDate datetime = '2010-4-1',
@DyEndDate datetime = '2010-4-30',
@GCLB varchar(1) = 'a'
AS
DECLARE @sql VARCHAR(4000)
BEGIN
SET @sql = 'SELECT a.pf AS 品番,
a.bf AS 部番,
a.pm AS 品名,
a.ggxh AS 规格,
b.jgjh AS 计划加工,
SUM(sl) AS 加工合计,
b.jgjh-SUM(sl) AS 差异'
SELECT @sql = @sql +
',SUM(CASE WHEN c.rq=''' + CONVERT(CHAR(10), rq, 120) + '''
THEN sl ELSE 0 END) AS ['+ RIGHT(CONVERT(CHAR(10), [rq], 120), 5) + ']'
FROM (select distinct rq from tpData where (rq BETWEEN @DyStartDate AND @DyEndDate) AND (bdid = 9)) t
SELECT @sql = @sql +
' FROM tpJiChuZiLiao a join tpJiHua b ON a.pf=b.pf join tpData c ON a.pf=c.pf
WHERE b.rq BETWEEN ''' + CONVERT(CHAR(10), @DyStartDate) + ''' AND ''' + CONVERT(CHAR(10), @DyEndDate)+ '''
AND c.rq BETWEEN ''' + CONVERT(CHAR(10), @DyStartDate) + ''' AND ''' + CONVERT(CHAR(10), @DyEndDate)+ '''
AND bdid=9
GROUP BY a.pf,a.bf,a.pm,a.ggxh,jgjh'
EXEC (@sql)这段代码我要写两遍,区别就在蓝色部分,分两种情况,bdid=9或bdid=10
如果@GCLB变量='a' 则bdid=9 否则 bdid=10
请问要如何修改?
@DyStartDate datetime = '2010-4-1',
@DyEndDate datetime = '2010-4-30',
@GCLB varchar(1) = 'a'
AS
DECLARE @sql VARCHAR(4000)
BEGIN
SET @sql = 'SELECT a.pf AS 品番,
a.bf AS 部番,
a.pm AS 品名,
a.ggxh AS 规格,
b.jgjh AS 计划加工,
SUM(sl) AS 加工合计,
b.jgjh-SUM(sl) AS 差异'
SELECT @sql = @sql +
',SUM(CASE WHEN c.rq=''' + CONVERT(CHAR(10), rq, 120) + '''
THEN sl ELSE 0 END) AS ['+ RIGHT(CONVERT(CHAR(10), [rq], 120), 5) + ']'
FROM (select distinct rq from tpData where (rq BETWEEN @DyStartDate AND @DyEndDate) AND (bdid = 9)) t
SELECT @sql = @sql +
' FROM tpJiChuZiLiao a join tpJiHua b ON a.pf=b.pf join tpData c ON a.pf=c.pf
WHERE b.rq BETWEEN ''' + CONVERT(CHAR(10), @DyStartDate) + ''' AND ''' + CONVERT(CHAR(10), @DyEndDate)+ '''
AND c.rq BETWEEN ''' + CONVERT(CHAR(10), @DyStartDate) + ''' AND ''' + CONVERT(CHAR(10), @DyEndDate)+ '''
AND bdid=9
GROUP BY a.pf,a.bf,a.pm,a.ggxh,jgjh'
EXEC (@sql)这段代码我要写两遍,区别就在蓝色部分,分两种情况,bdid=9或bdid=10
如果@GCLB变量='a' 则bdid=9 否则 bdid=10
请问要如何修改?
@DyStartDate datetime = '2010-4-1',
@DyEndDate datetime = '2010-4-30',
@GCLB varchar(1) = 'a'
AS
DECLARE @sql VARCHAR(4000)
BEGIN
SET @sql = 'SELECT a.pf AS 品番,
a.bf AS 部番,
a.pm AS 品名,
a.ggxh AS 规格,
b.jgjh AS 计划加工,
SUM(sl) AS 加工合计,
b.jgjh-SUM(sl) AS 差异'
SELECT @sql = @sql +
',SUM(CASE WHEN c.rq=''' + CONVERT(CHAR(10), rq, 120) + '''
THEN sl ELSE 0 END) AS ['+ RIGHT(CONVERT(CHAR(10), [rq], 120), 5) + ']'
FROM (select distinct rq from tpData where (rq BETWEEN @DyStartDate AND @DyEndDate) AND (bdid = 9)) t
SELECT @sql = @sql +
' FROM tpJiChuZiLiao a join tpJiHua b ON a.pf=b.pf join tpData c ON a.pf=c.pf
WHERE b.rq BETWEEN ''' + CONVERT(CHAR(10), @DyStartDate) + ''' AND ''' + CONVERT(CHAR(10), @DyEndDate)+ '''
AND c.rq BETWEEN ''' + CONVERT(CHAR(10), @DyStartDate) + ''' AND ''' + CONVERT(CHAR(10), @DyEndDate)+ '''
AND bdid='+(case when @GCLB ='a' then 9 else 10 end) +
'GROUP BY a.pf,a.bf,a.pm,a.ggxh,jgjh'
EXEC (@sql)
@DyStartDate datetime = '2010-4-1',
@DyEndDate datetime = '2010-4-30',
@GCLB varchar(1) = 'a'
AS
DECLARE @sql VARCHAR(4000)
BEGIN
declare @id int
select @id = (case when @GCLB ='a' then 9 else 10 end)
SET @sql = 'SELECT a.pf AS 品番,
a.bf AS 部番,
a.pm AS 品名,
a.ggxh AS 规格,
b.jgjh AS 计划加工,
SUM(sl) AS 加工合计,
b.jgjh-SUM(sl) AS 差异'
SELECT @sql = @sql +
',SUM(CASE WHEN c.rq=''' + CONVERT(CHAR(10), rq, 120) + '''
THEN sl ELSE 0 END) AS ['+ RIGHT(CONVERT(CHAR(10), [rq], 120), 5) + ']'
FROM (select distinct rq from tpData where (rq BETWEEN @DyStartDate AND @DyEndDate) AND (bdid = 9)) t
SELECT @sql = @sql +
' FROM tpJiChuZiLiao a join tpJiHua b ON a.pf=b.pf join tpData c ON a.pf=c.pf
WHERE b.rq BETWEEN ''' + CONVERT(CHAR(10), @DyStartDate) + ''' AND ''' + CONVERT(CHAR(10), @DyEndDate)+ '''
AND c.rq BETWEEN ''' + CONVERT(CHAR(10), @DyStartDate) + ''' AND ''' + CONVERT(CHAR(10), @DyEndDate)+ '''
AND bdid='+@GCLB +
'GROUP BY a.pf,a.bf,a.pm,a.ggxh,jgjh'
EXEC (@sql)
或者这样
消息 245,级别 16,状态 1,过程 p_p_JiaGongRiBao,第 29 行
在将 varchar 值 'SELECT a.pf AS 品番,
a.bf AS 部番,
a.pm AS 品名,
a.ggxh AS 规格,
b.jgjh AS 计划加工,
SUM(sl) AS 加工合计,
b.jgjh-SUM(sl) AS 差异,SUM(CASE WHEN c.rq='2010-04-01'
THEN sl ELSE 0 END) AS [04-01],SUM(CASE WHEN c.rq='2010-04-02'
THEN sl ELSE 0 END) AS [04-02],SUM(CASE WHEN c.rq='2010-04-03'
THEN sl ELSE 0 END) AS [04-03],SUM(CASE WHEN c.rq='2010-04-06'
THEN sl ELSE 0 END) AS [04-06],SUM(CASE WHEN c.rq='2010-04-07'
THEN sl ELSE 0 END) AS [04-07],SUM(CASE WHEN c.rq='2010-04-08'
THEN sl ELSE 0 END) AS [04-08],SUM(CASE WHEN c.rq='2010-04-09'
THEN sl ELSE 0 END) AS [04-09],SUM(CASE WHEN c.rq='2010-04-10'
THEN sl ELSE 0 END) AS [04-10],SUM(CASE WHEN c.rq='2010-04-11'
THEN sl ELSE 0 END) AS [04-11],SUM(CASE WHEN c.rq='2010-04-12'
THEN sl ELSE 0 END) AS [04-12],SUM(CASE WHEN c.rq='2010-04-13'
THEN sl ELSE 0 END) AS [04-13],SUM(CASE WHEN c.rq='2010-04-14'
THEN sl ELSE 0 END) AS [04-14],SUM(CASE WHEN c.rq='2010-04-15'
THEN sl ELSE 0 END) AS [04-15],SUM(CASE WHEN c.rq='2010-04-16'
THEN sl ELSE 0 END) AS [04-16],SUM(CASE WHEN c.rq='2010-04-17'
THEN sl ELSE 0 END) AS [04-17],SUM(CASE WHEN c.rq='2010-04-19'
THEN sl ELSE 0 END) AS [04-19],SUM(CASE WHEN c.rq='2010-04-20'
THEN sl ELSE 0 END) AS [04-20],SUM(CASE WHEN c.rq='2010-04-21'
THEN sl ELSE 0 END) AS [04-21],SUM(CASE WHEN c.rq='2010-04-22'
THEN sl ELSE 0 END) ...
错误出在字符串相连的问题上
ALTER PROCEDURE [dbo].[p_p_JiaGongRiBao]
@DyStartDate datetime = '2010-4-1',
@DyEndDate datetime = '2010-4-30',
@GCLB varchar(1) = 'a'
AS
DECLARE @sql VARCHAR(4000)
BEGIN
SET @sql = 'SELECT a.pf AS 品番,
a.bf AS 部番,
a.pm AS 品名,
a.ggxh AS 规格,
b.jgjh AS 计划加工,
SUM(sl) AS 加工合计,
b.jgjh-SUM(sl) AS 差异'
SELECT @sql = @sql +
',SUM(CASE WHEN c.rq=''' + CONVERT(CHAR(10), rq, 120) + '''
THEN sl ELSE 0 END) AS ['+ RIGHT(CONVERT(CHAR(10), [rq], 120), 5) + ']'
FROM (select distinct rq from tpData where (rq BETWEEN @DyStartDate AND @DyEndDate) AND (bdid = 9)) t
SELECT @sql = @sql +
' FROM tpJiChuZiLiao a join tpJiHua b ON a.pf=b.pf join tpData c ON a.pf=c.pf
WHERE b.rq BETWEEN ''' + CONVERT(CHAR(10), @DyStartDate) + ''' AND ''' + CONVERT(CHAR(10), @DyEndDate)+ '''
AND c.rq BETWEEN ''' + CONVERT(CHAR(10), @DyStartDate) + ''' AND ''' + CONVERT(CHAR(10), @DyEndDate)+ '''
AND bdid='+(case @GCLB when 'a' then '9' else '10' end)+
'GROUP BY a.pf,a.bf,a.pm,a.ggxh,jgjh'
EXEC (@sql)
END