开发者

Error on calling of Stored Procedure

开发者 https://www.devze.com 2023-01-14 09:02 出处:网络
I Create this stored procedure ALTER PROCEDURE [dbo].[Stock_Master_Sp] @common nvarchar(1)=\'\', @PK_ID int=0,

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...

0

精彩评论

暂无评论...
验证码 换一张
取 消