CREATE PROC R_ReportContractByWeek
(
@sql1 VARCHAR(50),
@sql2 VARCHAR(50)
)
AS
SELECT '电脑'AS Title,vc.contracttype,rc.CustomerID,vc.ContractSum,0 AS I_Amount,0 AS G_Amount
FROM dbo.v_ContractInfo_enable_rp vc
JOIN dbo.R_Customer rc ON vc.CompanyName=rc.CustomerID
WHERE 1=1 AND @sql1
UNION ALL
SELECT '手机'AS Title, '' AS contracttype ,''AS customerid,0 AS ContractSum, SUM(vt.Amount) AS I_Amount ,0 AS G_Amount
FROM dbo.v_ContractInfo_enable_rp vc
JOIN dbo.v_TaxInvoice_enable vt ON vc.contractno=vt.contractno
WHERE 1=1 AND @sql2
GROUP BY vt.ContractNo
GO
我这样做会报错
是不是要用到动态sql语句
该如何写?谢谢!
(
@sql1 VARCHAR(50),
@sql2 VARCHAR(50)
)
AS
SELECT '电脑'AS Title,vc.contracttype,rc.CustomerID,vc.ContractSum,0 AS I_Amount,0 AS G_Amount
FROM dbo.v_ContractInfo_enable_rp vc
JOIN dbo.R_Customer rc ON vc.CompanyName=rc.CustomerID
WHERE 1=1 AND @sql1
UNION ALL
SELECT '手机'AS Title, '' AS contracttype ,''AS customerid,0 AS ContractSum, SUM(vt.Amount) AS I_Amount ,0 AS G_Amount
FROM dbo.v_ContractInfo_enable_rp vc
JOIN dbo.v_TaxInvoice_enable vt ON vc.contractno=vt.contractno
WHERE 1=1 AND @sql2
GROUP BY vt.ContractNo
GO
我这样做会报错
是不是要用到动态sql语句
该如何写?谢谢!
CREATE PROC R_ReportContractByWeek
(
@sql1 VARCHAR(50),
@sql2 VARCHAR(50)
)
AS
EXEC('
SELECT
''电脑''AS Title,
vc.contracttype,
rc.CustomerID,
vc.ContractSum,
0 AS I_Amount,
0 AS G_Amount
FROM dbo.v_ContractInfo_enable_rp vc
JOIN dbo.R_Customer rc ON vc.CompanyName=rc.CustomerID
WHERE 1=1 AND '+@sql1 +'
UNION ALL
SELECT
''手机''AS Title,
'' AS contracttype ,
''AS customerid,
0 AS ContractSum,
SUM(vt.Amount) AS I_Amount ,
0 AS G_Amount
FROM dbo.v_ContractInfo_enable_rp vc
JOIN dbo.v_TaxInvoice_enable vt ON vc.contractno=vt.contractno
WHERE 1=1 AND '+@sql2+'
GROUP BY vt.ContractNo
')
GO
(
@sql1 VARCHAR(50),
@sql2 VARCHAR(50)
)
AS
EXEC ('
SELECT ''电脑'' AS Title,vc.contracttype,rc.CustomerID,vc.ContractSum,0 AS I_Amount,0 AS G_Amount
FROM dbo.v_ContractInfo_enable_rp vc
JOIN dbo.R_Customer rc ON vc.CompanyName=rc.CustomerID
WHERE 1=1 AND ' + @sql1 + '
UNION ALL
SELECT ''手机'' AS Title, '''' AS contracttype ,'''' AS customerid,0 AS ContractSum, SUM(vt.Amount) AS I_Amount ,0 AS G_Amount
FROM dbo.v_ContractInfo_enable_rp vc
JOIN dbo.v_TaxInvoice_enable vt ON vc.contractno=vt.contractno
WHERE 1=1 AND ' + @sql2 + '
GROUP BY vt.ContractNo
') GO
''AS customerid,应该改为 '''' AS contracttype ,
''''AS customerid,
在关键字 'UNION' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 12
在关键字 'GROUP' 附近有语法错误。
报这个错误。
是什么原因?
(
@sql1 VARCHAR(50),
@sql2 VARCHAR(50)
)
AS
exec('
SELECT ''电脑''AS Title,vc.contracttype,rc.CustomerID,vc.ContractSum,0 AS I_Amount,0 AS G_Amount
FROM dbo.v_ContractInfo_enable_rp vc
JOIN dbo.R_Customer rc ON vc.CompanyName=rc.CustomerID
WHERE 1=1 AND ' + @sql1 +
'UNION ALL
SELECT ''手机''AS Title, '' AS contracttype ''AS customerid,0 AS ContractSum, SUM(vt.Amount) AS I_Amount ,0 AS G_Amount
FROM dbo.v_ContractInfo_enable_rp vc
JOIN dbo.v_TaxInvoice_enable vt ON vc.contractno=vt.contractno
WHERE 1=1 AND ' + @sql2 +
' GROUP BY vt.ContractNo ')
go