存储过程的问题
CREATE PROCEDURE BaZqTJ2
@JNSARQ VARCHAR(15),
@JNJAJZRQ VARCHAR(15),
@QNSARQ VARCHAR(15),
@QNJARQ VARCHAR(15),
@strGGXXKMC VARCHAR(50) = ''
AS
BEGIN
CREATE TABLE #PJJWPC
(
ID INT IDENTITY PRIMARY KEY,
AJBS NUMERIC(16,0) NULL, -- 案件标识
KPLB INT NULL,
FYDM INT NULL,
SJFY INT NULL,
BATS INT NULL,
KCTS INT NULL,
SYTS INT NULL,
YCTS INT NULL,
AH VARCHAR(50) NULL,
LAAY VARCHAR(100) NULL,
LARQ DATE NULL,
JARQ DATE NULL,
CBR VARCHAR(40) NULL,
DSR VARCHAR(500) NULL,
SJY VARCHAR(40) NULL,
JNYJ smallint null,
JNWJ smallint null,
QNYJ smallint null,
QNWJ smallint null,
JNXS smallint null,
QNXS smallint null
)
declare @JNYJ1 VARCHAR(300)
declare @JNYJ2 VARCHAR(300)
declare @JNYJ3 VARCHAR(300)
declare @JNYJ4 VARCHAR(300)
declare @JNYJ5 VARCHAR(300)
SET @JNYJ1='(isnull(SPYJ,2)<>2 and JARQ>='''+@JNSARQ+''' and JARQ<='''+@JNJAJZRQ+''' and (AJJZJD>=4 or isnull(SPYJ,2)>2 or isnull(TQTJ,0)=1 or isnull(TQCS,0)=1))'
SET @JNYJ2='(isnull(SPYJ,2)<>2 and JARQ>='''+@JNSARQ+''' and JARQ<='''+@JNJAJZRQ+''' and (AJJZJD>=4 or isnull(SPYJ,2)>2))'
SET @JNYJ3='(JARQ>='''+@JNSARQ+''' and JARQ<='''+@JNJAJZRQ+''' and AJJZJD>=4)'
SET @JNYJ4='(cast(ZXSJ as date)>='''+@JNSARQ+''' and cast(ZXSJ as date)<='''+@JNJAJZRQ+''' and AJJZJD>=4)'
SET @JNYJ5='(isnull(SPYJ,2)<>2 and JARQ>='''+@JNSARQ+''' and JARQ<='''+@JNJAJZRQ+''')'
declare @QNYJ1 VARCHAR(300)
declare @QNYJ2 VARCHAR(300)
declare @QNYJ3 VARCHAR(300)
declare @QNYJ4 VARCHAR(300)
declare @QNYJ5 VARCHAR(300)
SET @QNYJ1='(isnull(SPYJ,2)<>2 and JARQ>='''+@QNSARQ+''' and JARQ<='''+@QNJARQ+''' and (AJJZJD>=4 or isnull(SPYJ,2)>2 or isnull(TQTJ,0)=1 or isnull(TQCS,0)=1))'
SET @QNYJ2='(isnull(SPYJ,2)<>2 and JARQ>='''+@QNSARQ+''' and JARQ<='''+@QNJARQ+''' and (AJJZJD>=4 or isnull(SPYJ,2)>2))'
SET @QNYJ3='(JARQ>='''+@QNSARQ+''' and JARQ<='''+@QNJARQ+''' and AJJZJD>=4)'
SET @QNYJ4='(cast(ZXSJ as date)>='''+@QNSARQ+''' and cast(ZXSJ as date)<='''+@QNJARQ+''' and AJJZJD>=4)'
SET @QNYJ5='(isnull(SPYJ,2)<>2 and JARQ>='''+@QNSARQ+''' and JARQ<='''+@QNJARQ+''')'
declare @JNWJ1 VARCHAR(300)
declare @JNWJ2 VARCHAR(300)
declare @JNWJ3 VARCHAR(300)
declare @JNWJ4 VARCHAR(300)
declare @JNWJ5 VARCHAR(300)
SET @JNWJ1='(isnull(SPYJ,2)<>2 and LARQ<='''+@JNJAJZRQ+''' and ((isnull(JARQ,''1900-1-1'')=''1900-1-1'' or (AJJZJD<4 and isnull(SPYJ,0)=1 and isnull(TQTJ,2)=2 and isnull(TQCS,2)=2))or(JARQ>'''+@JNJAJZRQ+''' and (AJJZJD>=4 or isnull(SPYJ,2)>2 or isnull(TQTJ,0)=1 or isnull(TQCS,0)=1))))'
SET @JNWJ2='(isnull(SPYJ,2)<>2 and LARQ<='''+@JNJAJZRQ+''' and ((isnull(JARQ,''1900-1-1'')=''1900-1-1'' or (AJJZJD<4 and isnull(SPYJ,0)=1))or(JARQ>'''+@JNJAJZRQ+''' and (AJJZJD>=4 or isnull(SPYJ,2)>2))))'
SET @JNWJ3='(LARQ<='''+@JNJAJZRQ+''' and ((isnull(JARQ,''1900-1-1'')=''1900-1-1'' or AJJZJD<4)or(JARQ>'''+@JNJAJZRQ+''' and AJJZJD>=4)))'
SET @JNWJ4='(BHRQ<='''+@JNJAJZRQ+''' and ((isnull(cast(ZXSJ as date),''1900-1-1'')=''1900-1-1'' or AJJZJD<4)or(cast(ZXSJ as date)>'''+@JNJAJZRQ+''' and AJJZJD>=4)))'
SET @JNWJ5='(isnull(SPYJ,2)<>2 and LARQ<='''+@JNJAJZRQ+''' and (isnull(JARQ,''1900-1-1'')=''1900-1-1'' or JARQ>'''+@JNJAJZRQ+'''))'
declare @QNWJ1 VARCHAR(300)
declare @QNWJ2 VARCHAR(300)
declare @QNWJ3 VARCHAR(300)
declare @QNWJ4 VARCHAR(300)
declare @QNWJ5 VARCHAR(300)
SET @QNWJ1='(isnull(SPYJ,2)<>2 and LARQ<='''+@QNJARQ+''' and ((isnull(JARQ,''1900-1-1'')=''1900-1-1'' or (AJJZJD<4 and isnull(SPYJ,0)=1 and isnull(TQTJ,2)=2 and isnull(TQCS,2)=2))or(JARQ>'''+@QNJARQ+''' and (AJJZJD>=4 or isnull(SPYJ,2)>2 or isnull(TQTJ,0)=1 or isnull(TQCS,0)=1))))'
SET @QNWJ2='(isnull(SPYJ,2)<>2 and LARQ<='''+@QNJARQ+''' and ((isnull(JARQ,''1900-1-1'')=''1900-1-1'' or (AJJZJD<4 and isnull(SPYJ,0)=1))or(JARQ>'''+@QNJARQ+''' and (AJJZJD>=4 or isnull(SPYJ,2)>2))))'
SET @QNWJ3='(LARQ<='''+@QNJARQ+''' and ((isnull(JARQ,''1900-1-1'')=''1900-1-1'' or AJJZJD<4)or(JARQ>'''+@QNJARQ+''' and AJJZJD>=4)))'
SET @QNWJ4='(BHRQ<='''+@QNJARQ+''' and ((isnull(cast(ZXSJ as date),''1900-1-1'')=''1900-1-1'' or AJJZJD<4)or(cast(ZXSJ as date)>'''+@QNJARQ+''' and AJJZJD>=4)))'
SET @QNWJ5='(isnull(SPYJ,2)<>2 and LARQ<='''+@QNJARQ+''' and (isnull(JARQ,''1900-1-1'')=''1900-1-1'' or JARQ>'''+@QNJARQ+'''))'
declare @JNXS1 VARCHAR(300)
declare @JNXS2 VARCHAR(300)
declare @JNXS3 VARCHAR(300)
SET @JNXS1 ='(isnull(SPYJ,2)<>2 and LARQ>='''+@JNSARQ+''' and LARQ<='''+@JNJAJZRQ+''')'
SET @JNXS2='(LARQ>='''+@JNSARQ+''' and LARQ<='''+@JNJAJZRQ+''')'
SET @JNXS3='(BHRQ>='''+@JNSARQ+''' and BHRQ<='''+@JNJAJZRQ+''')'
declare @QNXS1 VARCHAR(300)
declare @QNXS2 VARCHAR(300)
declare @QNXS3 VARCHAR(300)
SET @QNXS1 ='(isnull(SPYJ,2)<>2 and LARQ>='''+@QNSARQ+''' and LARQ<='''+@QNJARQ+''')'
SET @QNXS2='(LARQ>='''+@QNSARQ+''' and LARQ<='''+@QNJARQ+''')'
SET @QNXS3='(BHRQ>='''+@QNSARQ+''' and BHRQ<='''+@QNJARQ+''')'
DECLARE @BATS1 VARCHAR(100)
DECLARE @BATS2 VARCHAR(100)
DECLARE @BATS3 VARCHAR(100)
SET @BATS1='(DATEDIFF(DD,LARQ,JARQ)-ISNULL(KCTS,0))'
SET @BATS2='DATEDIFF(DD,LARQ,JARQ)'
SET @BATS3='DATEDIFF(DD,BHRQ,ZXSJ)'
DECLARE @strDSR1 VARCHAR(300)
DECLARE @strDSR2 VARCHAR(300)
DECLARE @strDSR3 VARCHAR(300)
SET @strDSR1="((CASE BGRMC WHEN NULL THEN '' ELSE BGRMC+';' END)+(CASE BHRMC WHEN NULL THEN '' ELSE BHRMC+';' END)+(CASE FDMSSSDSRMC WHEN NULL THEN '' ELSE FDMSSSDSRMC+';' END)+(CASE ZSRMC WHEN NULL THEN '' ELSE ZSRMC+';' END))"
SET @strDSR2="((CASE ZQRMC WHEN NULL THEN '' ELSE ZQRMC+';' END)+(CASE ZWRMC WHEN NULL THEN '' ELSE ZWRMC+';' END))"
SET @strDSR3="((CASE PCQQRMC WHEN NULL THEN '' ELSE PCQQRMC+';' END)+(CASE PCYWJGMC WHEN NULL THEN '' ELSE PCYWJGMC+';' END))"
EXEC('INSERT INTO #PJJWPC(AJBS,KPLB,FYDM,KCTS,SYTS,YCTS,AH,LAAY,LARQ,JARQ,DSR,SJY,BATS,JNYJ,JNWJ,QNYJ,QNWJ,JNXS,QNXS)
SELECT AJBS,KPLB,FYDM,SJFY,ISNULL(KCTS,0),ISNULL(SYTS,0),ISNULL(YCTS,0),AH,(SELECT AYNR FROM B_AY WHERE AYDM=B_XS.LAAY),LARQ,JARQ,'+@strDSR1+',SJY,'+@BATS1+',
CASE WHEN '+@JNYJ1+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNWJ1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNYJ1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNWJ1+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNXS1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNXS1+' THEN 1 ELSE 0 END
FROM B_XS ')
EXEC('INSERT INTO #PJJWPC(AJBS,KPLB,FYDM,KCTS,SYTS,YCTS,AH,LAAY,LARQ,JARQ,DSR,SJY,BATS,JNYJ,JNWJ,QNYJ,QNWJ,JNXS,QNXS)
SELECT AJBS,KPLB,FYDM,ISNULL(KCTS,0),ISNULL(SYTS,0),ISNULL(YCTS,0),AH,(SELECT AYNR FROM B_AY WHERE AYDM=B_MS.LAAY),LARQ,JARQ,DSRMC,SJY,'+@BATS1+',
CASE WHEN '+@JNYJ1+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNWJ1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNYJ1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNWJ1+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNXS1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNXS1+' THEN 1 ELSE 0 END
FROM B_MS ')
EXEC('INSERT INTO #PJJWPC(AJBS,KPLB,FYDM,KCTS,SYTS,YCTS,AH,LAAY,LARQ,JARQ,DSR,SJY,BATS,JNYJ,JNWJ,QNYJ,QNWJ,JNXS,QNXS)
SELECT AJBS,KPLB, FYDM ,ISNULL(KCTS,0),ISNULL(SYTS,0),ISNULL(YCTS,0),AH,(SELECT AYNR FROM B_AY WHERE AYDM=B_XZ.LAAY),LARQ,JARQ,DSRMC,SJY,'+@BATS1+',
CASE WHEN '+@JNYJ1+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNWJ1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNYJ1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNWJ1+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNXS1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNXS1+' THEN 1 ELSE 0 END
FROM B_XZ ')
EXEC('INSERT INTO #PJJWPC(AJBS,KPLB,FYDM,KCTS,SYTS,YCTS,AH,LAAY,LARQ,JARQ,DSR,SJY,BATS,JNYJ,JNWJ,QNYJ,QNWJ,JNXS,QNXS)
SELECT AJBS,KPLB, FYDM ,ISNULL(KCTS,0),ISNULL(SYTS,0),ISNULL(YCTS,0),AH,(SELECT AYNR FROM B_AY WHERE AYDM=B_SS.LAAY),LARQ,JARQ,SSSQZSRMC,SJY,'+@BATS1+',
CASE WHEN '+@JNYJ2+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNWJ2+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNYJ2+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNWJ2+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNXS1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNXS1+' THEN 1 ELSE 0 END
FROM B_SS ')
EXEC('INSERT INTO #PJJWPC(AJBS,KPLB,FYDM,KCTS,SYTS,YCTS,AH,LAAY,LARQ,JARQ,DSR,SJY,BATS,JNYJ,JNWJ,QNYJ,QNWJ,JNXS,QNXS)
SELECT AJBS,KPLB,FYDM,ISNULL(KCTS,0),ISNULL(SYTS,0),ISNULL(YCTS,0),AH,(SELECT AYNR FROM B_AY WHERE AYDM=B_ZX.LAAY),LARQ,JARQ,DSRMC,SJY,'+@BATS1+',
CASE WHEN '+@JNYJ2+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNWJ2+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNYJ2+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNWJ2+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNXS1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNXS1+' THEN 1 ELSE 0 END
FROM B_ZX ')
EXEC('INSERT INTO #PJJWPC(AJBS,KPLB,FYDM,KCTS,SYTS,YCTS,AH,LAAY,LARQ,JARQ,DSR,SJY,BATS,JNYJ,JNWJ,QNYJ,QNWJ,JNXS,QNXS)
SELECT AJBS,KPLB,FYDM,ISNULL(KCTS,0),ISNULL(SYTS,0),ISNULL(YCTS,0),AH,(SELECT AYNR FROM B_AY WHERE AYDM=B_PC.LAAY),LARQ,JARQ,'+@strDSR3+',SJY,'+@BATS1+',
CASE WHEN '+@JNYJ2+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNWJ2+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNYJ2+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNWJ2+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNXS1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNXS1+' THEN 1 ELSE 0 END
FROM B_PC ')
EXEC('INSERT INTO #PJJWPC(AJBS,KPLB,FYDM,KCTS,SYTS,YCTS,AH,LAAY,LARQ,JARQ,DSR,SJY,BATS,JNYJ,JNWJ,QNYJ,QNWJ,JNXS,QNXS)
SELECT AJBS,KPLB,FYDM,ISNULL(KCTS,0),ISNULL(SYTS,0),ISNULL(YCTS,0),AH,(SELECT AYNR FROM B_AY WHERE AYDM=B_QL.XGAY),LARQ,JARQ,DSRMC,SJY,'+@BATS1+',
CASE WHEN '+@JNYJ5+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNWJ5+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNYJ5+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNWJ5+' THEN 1 ELSE 0 END,
CASE WHEN '+@JNXS1+' THEN 1 ELSE 0 END,
CASE WHEN '+@QNXS1+' THEN 1 ELSE 0 END
FROM B_QL')
DELETE #PJJWPC WHERE JNYJ=0 AND QNYJ=0 AND JNWJ=0 AND QNWJ=0 AND JNXS=0 AND QNXS=0
SELECT AJBS,KPLB,FYDM,KCTS,SYTS,YCTS,AH,LAAY,LARQ,JARQ,DSR,SJY,BATS,JNYJ,JNWJ,QNYJ,QNWJ,JNXS,QNXS FROM #PJJWPC
END
我运行程序是出错 2011-7-7 16:25:13
1:错误代码:NSERT语句中的SELECT列表包含的项目多于INSERT列表。SELECT值的数目必须与INSERT 列的数目相匹配。
顺便也请高手讲一下 这个存储过程 我不太懂