不知道大家遇到过没有,在过滤数据的时候,往往要把数据分好几次的插入到临时表中,而有时会发现第二次插入数据到临时表时,会报
仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时,才能为表'card'中的标识列指定显式值,这样的错误。所以我把自己遇到的问题记录了下来,并解决了。以下是我解决后的源码:
ALTER PROC [dbo].[BeforePaymentC]
@eccUser int,
@wher varchar(2000)=''
AS
BEGIN
DECLARE @eccCode VARCHAR(8000)
DECLARE @ii INT
SET @eccCode=''
SET @ii=1
SELECT @eccCode=ISNULL(@eccCode,'')+CONVERT(VARCHAR(20),id)+',' FROM dbo.eccCollCard WHERE eccUser =@eccUser AND eccStatu=2
IF @eccCode<>''
BEGIN
SET @eccCode=SUBSTRING(@eccCode,0,LEN(@eccCode))
IF EXISTS(SELECT * FROM sysobjects WHERE NAME='#ttemp')
DROP TABLE #ttemp
--组装临时数据
WHILE @ii<=dbo.Get_StrArrayLength(@eccCode,',')
BEGIN
IF @ii=1
BEGIN
SELECT * INTO #ttemp FROM dbo.eccGoods WHERE ecgCK=dbo.Get_StrArrayStrOfIndex(@eccCode,',',@ii)
AND NOT EXISTS(SELECT *FROM eccPayment WHERE id=ecgXS and ecpStatu in(0,1))
SET IDENTITY_INSERT #ttemp ON
END
ELSE
BEGIN
INSERT INTO #ttemp (id,ecgCardCode,ecgStatus,ecgType,ecgInUser,ecgInDate,ecgChkInUser,ecgChkInDate,ecgGetUser,
ecgGetDate,ecgGetUser2,ecgGetDate2,ecgSale,ecgSalePrice,ecgUpPrice,ecgSaleDate,ecgPerid,ecgGetType,
ecgYearCard,ecgGoCar,ecgGoActDate,ecgPassWord,ecgGoUN,ecgGoMobi,ecgRC,ecgCK,
ecgXS,ecgStatu,ecgInterPrice) SELECT id,ecgCardCode,ecgStatus,ecgType,ecgInUser,ecgInDate,ecgChkInUser,ecgChkInDate,ecgGetUser,
ecgGetDate,ecgGetUser2,ecgGetDate2,ecgSale,ecgSalePrice,ecgUpPrice,ecgSaleDate,ecgPerid,ecgGetType,
ecgYearCard,ecgGoCar,ecgGoActDate,ecgPassWord,ecgGoUN,ecgGoMobi,ecgRC,ecgCK,
ecgXS,ecgStatu,ecgInterPrice FROM dbo.eccGoods WHERE ecgCK=dbo.Get_StrArrayStrOfIndex(@eccCode,',',@ii)
AND NOT EXISTS(SELECT *FROM eccPayment WHERE id=ecgXS and ecpStatu in(0,1))
END
SET @ii=@ii+1
END
IF @eccCode<>''
BEGIN
SET IDENTITY_INSERT #ttemp OFF
IF @wher='' OR @wher IS NULL OR LEN(@wher)=0
SELECT *FROM #ttemp
ELSE
EXEC('SELECT *FROM #ttemp WHERE '+ @wher)
END
END
END
go