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

lovespl 2006-02-18 05:16:28
小弟有個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
...全文
87 2 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
lovespl 2006-02-18
  • 打赏
  • 举报
回复
可是我在程式裡這樣做測試也不行,你說是單引號,你說的是在本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)
mm2love2zz 2006-02-18
  • 打赏
  • 举报
回复
程序调用的时候的问题!估计又是单引号的问题啊~~~
还在为答辩发愁? 熬夜改PPT?怕被导师怼到自闭? 别慌!这份「答辩通关模板」让你稳过! 为什么你需要这份答辩指南? 15个高频问题+标准答案——导师最爱问的,我们都帮你整理好了! 万能道歉话术——被提问不会答?直接套模板,优雅化解尴尬! 逻辑清晰的答辩框架——从自我介绍到总结,照着说就能拿高分! 适用于任何专业——文科/理科/工科,统统能用! 适合谁用? 答辩在即,但还没准备好的同学 怕临场发挥不好,想要提前演练的人 想轻松过关,不被导师刁难的毕业生 模板内容预览 1. 自我介绍(直接套用,稳拿印象分) "尊敬的各位老师上午好,我是XX级XX专业的XXX,我的论文题目是《XXXX》……" 2. 研究背景与意义(3句话讲清楚,导师点头认可) "近年来XX问题备受关注,但现有研究存在XX不足,因此本文……" 3. 论文框架(逻辑清晰,让导师眼前一亮) "本文分为五个部分:背景分析→理论基础→问题研究→解决方案→结论展望……" 4. 创新点与不足(巧妙表达,展现学术严谨) "本文创新在于XX方法的应用,但由于数据限制,仍有改进空间……" 5. 答辩话术(15个高频问题+标准答案) 导师问:"为什么选这个题目?" 标准答:"谢谢老师提问,我在查阅文献时发现XX领域研究较少……" 6. 万能道歉模板(被怼也不慌) "感谢老师指正,这个问题确实是我考虑不周,会后我会进一步完善……"

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧