I Create this stored procedure
ALTER PROCEDURE [dbo].[Stock_Master_Sp]
@common nvarchar(1)='',
@PK_ID int=0,
@FK_StoneCategory_Master int=0,
@FK_StoneType_Master int=0,
@FK_StoneName_Master int=0,
@StoneSize nvarchar(50)='',
@StoneWeight nvarchar(50)='',
@FK_StoneShape_Master int=0,
@StoneStrange int=0,
@FK_StoneQuality_Master int=0,
@RatePerStone decimal(18, 2)=0.0,
@FK_User_Master int=0,
@QuantityInStock int=0,
@QuantityOnConsignment int=0,
@Code nvarchar(max)=''
AS
BEGIN
declare @pk int
declare @rate decimal(18,2)
declare @quantity decimal(18,2)
IF @common='t'
BEGIN
IF EXISTS (SELECT *
FROM Stock_Master
WHERE FK_StoneCategory_Master=@FK_StoneCategory_Master AND
FK_StoneType_Master=@FK_StoneType_Master AND
FK_StoneName_Master=@FK_StoneName_Master AND
dbo.TRIM(LOWER(StoneSize))=dbo.TRIM(LOWER(@StoneSize)) AND
dbo.TRIM(LOWER(StoneWeight))=dbo.TRIM(LOWER(@StoneWeight)) AND
FK_StoneShape_Master=@FK_StoneShape_Master AND
dbo.TRIM(LOWER(StoneStrange))=dbo.TRIM(LOWER(@StoneStrange)) AND
FK_StoneQuality_Master=@FK_StoneQuality_Master AND
dbo.TRIM(LOWER(Code))=dbo.TRIM(LOWER(@Code)) AND
FK_User_Master=@FK_User_Master)
BEGIN
SELECT @pk=PK_ID, @rate=RatePerStone, @quantity=QuantityInStock
FROM Stock_Master
WHERE FK_StoneCategory_Master=@FK_StoneCategory_Master AND
FK_StoneType_Master=@FK_StoneType_Master AND
FK_StoneName_Master=@FK_StoneName_Master AND
dbo.TRIM(LOWER(StoneSize))=dbo.TRIM(LOWER(@StoneSize)) AND
dbo.TRIM(LOWER(StoneWeight))=dbo.TRIM(LOWER(@StoneWeight)) AND
FK_StoneShape_Master=@FK_StoneShape_Master AND
dbo.TRIM(LOWER(StoneStrange))=dbo.TRIM(LOWER(@StoneStrange)) AND
FK_StoneQuality_Master=@FK_StoneQuality_Master AND
dbo.TRIM(LOWER(Code))=dbo.TRIM(LOWER(@Code)) AND
FK_User_Master=@FK_User_Master
UPDATE Stock_Master
SET RatePerStone = (@rate+@RatePerStone)/2,
QuantityInStock=@QuantityInStock + @quantity
WHERE PK_ID=@pk
END
ELSE
BEGIN
INSERT INTO Stock_Master ([FK_StoneCategory_Master]
,[FK_StoneType_Master]
,[FK_StoneName_Master]
,[StoneSize]
,[StoneWeight]
,[FK_StoneShape_Master]
,[StoneStrange]
,[FK_StoneQuality_Master]
,[RatePerStone]
,[FK_User_Master]
,[QuantityInStock]
,[Code])
VALUES(@FK_StoneCategory_Master
,@FK_StoneType_Master
,@FK_StoneName_Master
,dbo.TRIM(LOWER(@StoneSize))
,dbo.TRIM(LOWER(@StoneWeight))
,@FK_StoneShape_Master
,@StoneStrange
,@FK_StoneQuality_Master
,@RatePerStone
,@FK_User_Master
,@QuantityInStock
,dbo.TRIM(LOWER(@Code)))
END
END
END
I am calling like that
Stock_Master_Sp 't','','4','4','6','2222','12','3','2','2','470','1','12','0','2112'
but i got this error at calling time
Location: memilb.cpp:1624
Expression: pilb->m_cRef == 0
SPID: 60
Process ID: 1628
Msg 3624, Level 20, State 1, Procedure Stock_Master_Sp, Line 27
A system assertion check has failed. Check the SQL Server error log for details
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
have an开发者_StackOverflowy solution plz tell me
if I call like than its working
Stock_Master_Sp
Stock_Master_Sp 't','','4','4','6','2222','12','3','2','2','470','1','12','0','2112'
to overcome this issue, remove lower() function from you sp wherever its have been occured. it will be fine afterwards.
you can treat it as a bug from microsoft itself.
Known bug, reported on MS COnnect?
This suggests SQL Server 2005 SP3 may fix it...
精彩评论