我创建了一个存储过程，以允许外部系统创建通常由Web界面处理的工程通知。为了防止输入错误的数据，我在执行时添加了一些错误提示。但是，我在尝试运行时遇到一些转换错误。

示例：

EXEC UDEF_Insert_EN 'WRONGNAME','2316',1002,'Test SP Title','Test SP Comments','A123-457','Z'

返回

Conversion failed when converting the varchar value '' to data type int 

存储过程如下

--EXEC UDEF_Insert_EN 'KPark','Z'
--SELECT * FROM _ER2_EN_Details


ALTER Procedure [dbo].[UDEF_Insert_EN]
(
    @username           nvarchar(50),@ER_No              float,@EN_Type            int,@EN_Title           nvarchar(50),@EN_Comments        nvarchar(200),@PartNo             nvarchar(16),@PartRev            nvarchar(4)
)
As

Declare 
    @ER_ID              float,@User_ID            int,@EN_No              float,@err_text           nvarchar (200),@err_out            tinyint

BEGIN

    Set @err_out = 0
    Set @err_text = 'ERRORS: '
    Set @EN_No = 0


    -- Check if supplied ER Number is valid and if so populate @ER_ID and @EN_No
    If @ER_No in (Select ER_Number from _ER2_Details)
    Begin
        Set @ER_ID = (Select Id from _ER2_Details where @ER_No = ER_Number)     
        Set @EN_No = IsnULL((Select EN_Index from _ER2_Details where @ER_ID = _ER2_Details.Id),1)
    End
    Else
    Begin
        Set @err_text = Char(13) + @ER_No + ' is not a valid ER Number'
        Set @err_out = 1
    End

    -- Check if supplied username is valid and lookup SinglePoint UserID
    If @username in (Select USR_NAME from USERS)
        Set @User_ID = (Select USR_ID from USERS where USR_NAME = @username)
    Else
    Begin
        Set @err_text = Char(13) + @err_text + @username + ' is not a valid Singlepoint user. Ensure the username matches the Singlepoint Login'
        Set @err_out = 1
    End

    -- Validate @EN_Type parameter
    If  @EN_Type in 
       (    1000,-- New Part
            1001,-- Revised Part
            1002,-- New Assembly
            1003,-- Revised Assembly
            1004,-- New Weldment
            1005,-- Revised Weldment
            1008,-- Third Party Part (8000)
            1009  -- Quick Change
        )   
        Set @EN_Type = @EN_Type
    Else
    Begin
        Set @err_text = Char(13) + @err_text + ' Invalid EN Type'
        Set @err_out = 1
    End


    if @PartNo in (Select EN_Part_Doc_Number from _ER2_EN_Details where @ER_ID = _ER2_EN_Details.ER_ID) and
       @PartRev in (Select EN_Part_Doc_Revision from _ER2_EN_Details where @ER_ID = _ER2_EN_Details.ER_ID)
    Begin
        Set @err_text = Char(13) + @err_text + ' Duplicate EN'
        Set @err_out = 1
    End

    -- Begin Insert
    If @err_out = 0
    BEGIN
    INSERT INTO _ER2_EN_Details
    (

        [sys$CreatedBy]     -- Looked up User ID from @username parameter,[sys$CreatedAt]     -- DateTime,[sys$ModifiedBy]    -- Looked up User ID from @username parameter,[sys$ModifiedAt]    -- DateTime,[ER_ID]             -- Looked up from parameter @ER_No,[EN_Number]         -- Looked up from EN_Index from ER record,[EN_Part_Doc_Number]    -- @PartNo parameter,[EN_Part_Doc_Revision]  -- @PartRev parameter,[EN_Comments]           -- @EN_Comments parameter - Part or revision description,[Date_Created]          -- DateTime,[EN_Status]             -- 1000 (Work in Progress Singlepoint EN status),[EN_Type]               -- @EN_Type parameter,[EN_Title]              -- @EN_Title parameter - Part Name,[EN_Source]             -- 1002 - Vault Import
    )
    Values 
    (

         @User_ID,GETDATE(),@User_ID,@ER_ID,@EN_No,@PartNo,@PartRev,@EN_Comments,1000               -- Status = Work In Progres,@EN_Type,@EN_Title,1002               -- Source = Vault Import
    )

    -- UPDATE _ER2_Details record to increment EN_Index
    UPDATE _ER2_Details
        SET EN_Index = EN_Index + 1
    WHERE _ER2_Details.Id = @ER_ID

    -- Return success message with Singlepoint EN number
    Set @err_text = 'SUCCESS: EN-' + @ER_No + '-' + @EN_No

    END

    Print @err_text
END

这是插入的表定义