救命啊!!!!!!!!!!!!!!

[lovespl]

小弟有個SP程式，單獨在SQLSERVER裡調用沒有返回類似“影響一筆”這個的提示結果，
在程式裡調用總是出現問題，不知是哪裡出了問題，還請大蝦們多指教啊？？？





SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


ALTER PROCEDURE SPINS_SAMPLE_RULE
@SAMPLE_ID NVARCHAR(15),
@TYPE NVARCHAR(1),
@OPT1_ID NVARCHAR(1),
@VALUE1_TYPE NVARCHAR(1),
@VALUE1 NUMERIC(17,2),
@VALUE1_ID NVARCHAR(20),
@OPT2_ID NVARCHAR(1),
@VALUE2_TYPE NVARCHAR(1),
@VALUE2 NUMERIC(17,2),
@VALUE2_ID NVARCHAR(20),
@OPT3_ID NVARCHAR(1),
@VALUE3_TYPE NVARCHAR(1),
@VALUE3 NUMERIC(17,2),
@VALUE3_ID NVARCHAR(2000)
AS

SET NOCOUNT ON
DECLARE @@NEW_FORMULA_ID NVARCHAR(4),
@@VALUE1 NVARCHAR(1000),
@@VALUE2 NVARCHAR(1000),
@@VALUE3 NVARCHAR(1000),
@@VALUE1_SQL NVARCHAR(2000),
@@VALUE2_SQL NVARCHAR(2000),
@@VALUE3_SQL NVARCHAR(2000),
@@PSU_PK NVARCHAR(50),
@@OPT1_DES NVARCHAR(5),
@@OPT2_DES NVARCHAR(5),
@@OPT3_DES NVARCHAR(5),
@@FORMULA_CONTENT NVARCHAR(2000),
@@FORMULA_SQL NVARCHAR(2000),
@@SETVALUENUM INT,
@@ERROR_NUM INT
SET @@VALUE1=''
SET @@VALUE2=''
SET @@VALUE3=''

IF ( EXISTS(SELECT FORMULA_ID FROM SRP_SAMPLE_RULE WHERE SAMPLE_ID=@SAMPLE_ID AND TYPE=@TYPE ))

BEGIN

SELECT @@NEW_FORMULA_ID=MAX(FORMULA_ID) FROM SRP_SAMPLE_RULE WHERE SAMPLE_ID=@SAMPLE_ID AND TYPE=@TYPE
SET @@NEW_FORMULA_ID=@@NEW_FORMULA_ID+1
END
ELSE
BEGIN
SET @@NEW_FORMULA_ID='1'
END
IF @VALUE1_TYPE='0'
BEGIN
IF @VALUE1 IS NOT NULL
BEGIN
SET @@VALUE1=CAST(@VALUE1 AS NVARCHAR)
END
ELSE
BEGIN
SET @@VALUE1=''
END
END
ELSE IF @VALUE1_TYPE='1'
BEGIN
--SET @@VALUE1=dbo.FNGET_NAME('SRP_FACTOR_M',@VALUE1_ID)
SELECT @@VALUE1=FACT_NAME FROM SRP_FACTOR_M WHERE FACT_ID=@VALUE1_ID
END
IF @VALUE2_TYPE='0'
BEGIN
IF @VALUE2 IS NOT NULL
BEGIN
SET @@VALUE2=CAST(@VALUE2 AS NVARCHAR)
END
ELSE
BEGIN
SET @@VALUE2= ''
END
END
ELSE IF @VALUE2_TYPE='1'
BEGIN
--SET @@VALUE2=dbo.FNGET_NAME('SRP_FACTOR_M',@VALUE2_ID)
SELECT @@VALUE2=FACT_NAME FROM SRP_FACTOR_M WHERE FACT_ID=@VALUE2_ID
END
SELECT @@OPT1_DES=OPT_DES FROM SRP_RULL_OPT_CODE WHERE OPT_TYPE='0' AND OPT_ID=@OPT1_ID
SELECT @@OPT2_DES=OPT_DES FROM SRP_RULL_OPT_CODE WHERE OPT_TYPE='1' AND OPT_ID=@OPT2_ID
SELECT @@OPT3_DES=OPT_DES FROM SRP_RULL_OPT_CODE WHERE OPT_TYPE='2' AND OPT_ID=@OPT3_ID
IF @VALUE3_TYPE='0'
BEGIN
IF @VALUE3 IS NOT NULL
BEGIN
SET @@VALUE3=CAST(@VALUE3 AS NVARCHAR)
END
ELSE
BEGIN
SET @@VALUE3 = ''
END
END
ELSE IF @VALUE3_TYPE='1'
BEGIN
SET @@VALUE3= ' ( '+@VALUE3_ID+' )'
END
/*IF @@OPT1_DES != '' AND @@OPT2_DES != '' AND @@OPT3_DES != ''
BEGIN
SET @@FORMULA_CONTENT=@@OPT1_DES+@@VALUE1+@@OPT2_DES+@@VALUE2+@@OPT3_DES+@@VALUE3
END
ELSE
--當OPT1_DES為空的時候, 也可能會有VALUE1,所以要判斷
IF @@OPT1_DES = ''
BEGIN
IF @@VALUE1 != ''
SET @@FORMULA_CONTENT=@@VALUE1 + @@OPT2_DES+@@VALUE2+@@OPT3_DES+@@VALUE3
ELSE
SET @@FORMULA_CONTENT=@@OPT2_DES+@@VALUE2+@@OPT3_DES+@@VALUE3
END
ELSE
IF @@OPT2_DES = ''
SET @@FORMULA_CONTENT=@@OPT1_DES+@@VALUE1+@@OPT3_DES+@@VALUE3
ELSE
IF @@OPT3_DES = ''
SET @@FORMULA_CONTENT=@@OPT1_DES+@@VALUE1+@@OPT2_DES+@@VALUE2
--PRINT '@@FORMULA_CONTENT ='+@@FORMULA_CONTENT
*/
SET @@FORMULA_CONTENT= ' ' + @@OPT1_DES+ ' ' + @@VALUE1+@@OPT2_DES+@@VALUE2+@@OPT3_DES+@@VALUE3
--組合 FORMULA SQL
SET @@VALUE1_SQL=''
SET @@VALUE2_SQL=''
SET @@VALUE3_SQL=''
--VALUE1
IF @VALUE1_TYPE='0'
BEGIN
IF @VALUE1 IS NOT NULL
BEGIN
SET @@VALUE1= CAST(@VALUE1 AS NUMERIC(17,2))
END
ELSE
BEGIN
SET @@VALUE1 = ''
END
END
ELSE IF @VALUE1_TYPE='1'
BEGIN
SET @@VALUE1_SQL = dbo.FNGET_FACTOR_SQL(@VALUE1_ID)
SET @@VALUE1= ' ( '+ @@VALUE1_SQL +' ) '
END
--VALUE2
IF @VALUE2_TYPE='0'
BEGIN
IF @VALUE2 IS NOT NULL
BEGIN
SET @@VALUE2= CAST(@VALUE2 AS NUMERIC(17,2))
END
ELSE
BEGIN
SET @@VALUE2 = ''
END
END
ELSE IF @VALUE2_TYPE='1'
BEGIN
SET @@VALUE2_SQL = dbo.FNGET_FACTOR_SQL(@VALUE2_ID)
SET @@VALUE2= ' ( ' + @@VALUE2_SQL +' ) '
END
--VALUE3
IF @VALUE3_TYPE='0'
BEGIN
IF @VALUE3 IS NOT NULL
BEGIN
SET @@VALUE3= CAST(@VALUE3 AS Numeric(17,2))
END
ELSE
BEGIN
SET @@VALUE3 = ''
END
END
SET @@SETVALUENUM=0
IF @VALUE3_TYPE='1'
BEGIN
SET @@SETVALUENUM= CHARINDEX(',',@VALUE3_ID)
IF @@SETVALUENUM>1
BEGIN
SET @@VALUE3= ' ( '+@VALUE3_ID+' )'
END
ELSE
BEGIN
SET @@VALUE3= '''' + @VALUE3_ID + ''''
END

END
--組合 FORMULA_SQL
IF (@@OPT3_DES = '=' AND @@SETVALUENUM>1)
BEGIN
DECLARE @A NVARCHAR(500)
DECLARE @CURVAL NVARCHAR(2000)
DECLARE @ITEM NVARCHAR(50)
DECLARE @LIST NVARCHAR(2000)
SET @A = @VALUE3_ID + ','
SET @CURVAL = ''
SET @ITEM = ''
SET @LIST = ''

SELECT @ITEM = SUBSTRING(@A, 1, CHARINDEX(',', @A)-1 )
SET @LIST = @LIST + '(''' + @ITEM + ''''
SELECT @A = SUBSTRING(@A, CHARINDEX(',',@A)+1, DATALENGTH(@A))
WHILE ( CHARINDEX ( ',', @A) ) > 0
BEGIN

SELECT @ITEM = SUBSTRING(@A, 1, CHARINDEX(',', @A)-1 )
SET @LIST = @LIST + ',''' + @ITEM + ''''
SELECT @A = SUBSTRING(@A, CHARINDEX(',',@A)+1, DATALENGTH(@A))
END
SET @LIST = @LIST + ')'
SET @@FORMULA_SQL = ' ' + @@OPT1_DES + ' (EXISTS('+@@VALUE1_SQL + @@OPT2_DES + @@VALUE2_SQL + ' AND FACT_VALUE IN '+ @LIST+'))'

END
ELSE
BEGIN
SET @@FORMULA_SQL = ' ' + @@OPT1_DES + '(' +@@VALUE1 + @@OPT2_DES + @@VALUE2 + ')'+ @@OPT3_DES + @@VALUE3
END
PRINT '@@FORMULA_SQL = '+@@FORMULA_SQL
--SET @@FORMULA_SQL=@@OPT1_DES+@@VALUE1_SQL+@@OPT2_DES+@@VALUE2_SQL+@@OPT3_DES+@@VALUE3_SQL
--SET @@PSU_PK=@MODEL_ID+'-'+@MODEL_VERSION+'-'+@USE_TYPE+'-'+@RULL_ID+'-'+@@NEW_FORMULA_ID
BEGIN TRANSACTION

/* PART 4 */
INSERT INTO SRP_SAMPLE_RULE(SAMPLE_ID,TYPE,FORMULA_ID,OPT1_ID,VALUE1_TYPE,VALUE1,VALUE1_ID,OPT2_ID,VALUE2_TYPE,VALUE2,VALUE2_ID, OPT3_ID,VALUE3_TYPE,VALUE3,FORMULA_CONTENT,FORMULA_SQL, VALUE3_ID)
VALUES( @SAMPLE_ID,@TYPE,@@NEW_FORMULA_ID,@OPT1_ID,@VALUE1_TYPE,@VALUE1,@VALUE1_ID,@OPT2_ID,@VALUE2_TYPE,@VALUE2,@VALUE2_ID,@OPT3_ID,@VALUE3_TYPE,@VALUE3,@@FORMULA_CONTENT,@@FORMULA_SQL, @VALUE3_ID)

SELECT @@ERROR_NUM = @@ERROR
IF @@ERROR_NUM != 0

BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
--PRINT @@ERROR_NUM
RETURN @@ERROR_NUM

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[mm2love2zz]

程序调用的时候的问题!估计又是单引号的问题啊~~~

• 打赏
• 举报

回复 赞

[lovespl]

可是我在程式裡這樣做測試也不行，你說是單引號，你說的是在本SP裡的單引號問題嗎？

String mSQL = "EXEC SPINS_SAMPLE_RULE 'SIM0002',@S,'1','0','1.00','','1','0','1.00','','0','1','1.00',''";
se.executeUpdateSP(mSQL,pa)

• 打赏
• 举报

回复 赞