CREATE PROCEDURE A
(
@PRD_NO varchar(MAX) ,
@CUST varchar(MAX) ,
@DD_S VARCHAR(10),
@DD_E VARCHAR(10),
@PT VARCHAR(30),
@TT VARCHAR(30)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PARM NVARCHAR(MAX) = N'',
@SQLCOMMAND NVARCHAR(MAX)= N''
SET @SQLCOMMAND = 'SELECT zs,日期,NAME,PRD_NO,HS_UP1,NS_UP1,HS_PJS,HS_PJS_趨勢,HS_ZS,HS_ZS_趨勢,HS_ZWS,HS_ZWS_趨勢,NS_PJS,NS_PJS_趨勢,NS_ZS,NS_ZS_趨勢,NS_ZWS,NS_ZWS_趨勢,MAX(CAST(日期 AS VARCHAR(10))=@DD_E,MIN(CAST(日期 AS VARCHAR(10))=@DD_S from XJD WHERE PRD_NO=@PRD_NO'
IF (@TT='HS' AND @PT='A')
SET @SQLCOMMAND = @SQLCOMMAND + ' AND cast(HS_UP1 as VARCHAR(20))=@TT' + ' AND cast(HS_PJS as VARCHAR(20))=@PT'
IF (@TT='HS' AND @PT='B')
SET @SQLCOMMAND = @SQLCOMMAND + ' AND cast(HS_UP1 as VARCHAR(20))=@TT' + ' AND cast(HS_ZS as VARCHAR(20))=@PT'
IF (@TT='HS' AND @PT='C')
SET @SQLCOMMAND = @SQLCOMMAND + ' AND cast(HS_UP1 as VARCHAR(20))=@TT' + ' AND cast(HS_ZWS as VARCHAR(20)=@PT'
IF (@TT='NS' AND @PT='A')
SET @SQLCOMMAND = @SQLCOMMAND + ' AND cast(NS_UP1 as VARCHAR(20))=@TT' + ' AND cast(NS_PJS as VARCHAR(20))=@PT'
IF (@TT='NS' AND @PT='B')
SET @SQLCOMMAND = @SQLCOMMAND + ' AND cast(NS_UP1 as VARCHAR(20))=@TT' + ' AND cast(NS_ZS as VARCHAR(20))=@PT'
IF (@TT='NS' AND @PT='C')
SET @SQLCOMMAND = @SQLCOMMAND + ' AND cast(NS_UP1 as VARCHAR(20))=@TT' + ' AND cast(NS_ZWS as VARCHAR(20))=@PT'
IF (@CUST='')
set @sqlcommand = @SQLCOMMAND + ' AND NAME=@CUST'
IF (@CUST='aaa,bbb,ccc')
SET @SQLCOMMAND =@SQLCOMMAND + ' AND @CUST IN (SELECT DISTINCT NAME FROM CUST)'
SET @PARM ='
@PRD_NO varchar(MAX) OUTPUT,
@CUST varchar(MAX) OUTPUT,
@DD_S VARCHAR(10) OUTPUT,
@DD_E VARCHAR(10) OUTPUT,
@PT VARCHAR(30) OUTPUT,
@TT VARCHAR(30) OUTPUT'
PRINT @SQLCOMMAND
EXEC SP_EXECUTESQL @SQLCOMMAND,@PARM,
@PRD_NO = @PRD_NO,
@CUST = @CUST,
@PT = @PT,
@TT = @TT,
@DD_S = @DD_S,
@DD_E = @DD_E
END
DECLARE
@PRD_NO varchar(MAX) ,
@CUST varchar(MAX) ,
@DD_S VARCHAR(10),
@DD_E VARCHAR(10),
@PT VARCHAR(30),
@TT VARCHAR(30)
EXEC A
@PRD_NO = @PRD_NO,
@TT = 'NS',
@PT = 'A',
@CUST = 'aaa,bbb,ccc',
@DD_S = @DD_S,
@DD_E = @DD_E;
SELECT @DD_S,@DD_E,@PT,@TT,@PRD_NO,@CUST;
DROP PROCEDURE A
運行結果報錯DECLARE
@PRD_NO varchar(MAX) ,
@CUST varchar(MAX) ,
@DD_S VARCHAR(10),
@DD_E VARCHAR(10),
@PT VARCHAR(30),
@TT VARCHAR(30)
EXEC A
@PRD_NO = @PRD_NO,
@TT = 'NS',
@PT = 'A',
@CUST = 'aaa,bbb,ccc',
@DD_S = @DD_S,
@DD_E = @DD_E;
SELECT @DD_S,@DD_E,@PT,@TT,@PRD_NO,@CUST;
> Msg 102, Level 15, State 1, Server XX-ERP, Procedure , Line 0
'=' 附近有語法錯誤。
> SELECT zs,日期,NAME,PRD_NO,HS_UP1,NS_UP1,HS_PJS,HS_PJS_趨勢,HS_ZS,HS_ZS_趨勢,HS_ZWS,HS_ZWS_趨勢,NS_PJS,NS_PJS_趨勢,NS_ZS,NS_ZS_趨勢,NS_ZWS,NS_ZWS_趨勢,MAX(CAST(日期 AS VARCHAR(10))=@DD_E,MIN(CAST(日期 AS VARCHAR(10))=@DD_S from XJD WHERE PRD_NO=@PRD_NO AND cast(NS_UP1 as VARCHAR(20))=@TT AND cast(NS_PJS as VARCHAR(20))=@PT AND @CUST IN (SELECT DISTINCT NAME FROM CUST)
> [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]'=' 附近有語法錯誤。 (102)
[01000] [Microsoft][SQL Server Native Client 10.0][SQL Server]SELECT zs,日期,NAME,PRD_NO,HS_UP1,NS_UP1,HS_PJS,HS_PJS_趨勢,HS_ZS,HS_ZS_趨勢,HS_ZWS,HS_ZWS_趨勢,NS_PJS,NS_PJS_趨勢,NS_ZS,NS_ZS_趨勢,NS_ZWS,NS_ZWS_趨勢,MAX(CAST(日期 AS VARCHAR(10))=@DD_E,MIN(CAST(日期 AS VARCHAR(10))=@DD_S from XJD WHERE PRD_NO=@PRD_NO AND cast(NS_UP1 as VARCHAR(20))=@TT AND cast(NS_PJS as VARCHAR(20))=@PT AND @CUST IN (SELECT DISTINCT NAME FROM CUST) (0)
> 時間: 0.019s
uj5u.com熱心網友回復：
幫我看看錯在哪了？
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/9773.html
標籤：應用實例
下一篇：指定行相加