使用存储过程进行插入，更新和删除

问题描述：

我正在尝试使用单个存储过程进行插入，更新，删除.插入工作正常，但对于删除它会引发错误，即-

I am trying to Insert, Update, Delete using single stored procedure. Insertion is working correctly but for Deletion it raise error that-

@ID不是过程hrm_Langauges的参数.

@ID is not a parameter for procedure hrm_Langauges.

我正在尝试使用 id 列进行删除.

I am trying to delete using the id column.

这是我的存储过程.

ALTER PROCEDURE [dbo].[hrm_Langauges]
(
    @Name varchar(120) = 0,
    @CreatedOn datetime = 0,
    @UpdatedOn datetime = 0,
    @CreatedBy bigint = 0,
    @UpdatedBy bigint = 0,
    @IsDeleted bit = 0,
    @status as varchar(50)
)
AS
    Declare @ID int;

    Select @ID = count(ID) + 1 from [dbo].[Languages]

    if(@status = 'Display')
    BEGIN
        SELECT ID FROM [dbo].[Languages] WHERE Name=@Name
    END
    else if(@status = 'Add')
    BEGIN
        IF EXISTS(SELECT Name FROM [dbo].[Languages] WHERE Name = @Name and IsDeleted=0)
    Begin
    Return 0
    End
    Else
        INSERT INTO [dbo].[Languages](Name,CreatedOn,CreatedBy) VALUES(@Name,@CreatedOn,@CreatedBy)
    END
    else if(@status = 'Update')
    BEGIN
        UPDATE [dbo].[Languages] Set Name=@Name,UpdatedOn=@UpdatedOn, UpdatedBy=@UpdatedBy WHERE ID=@ID
    END
    else if(@status = 'Delete')
    BEGIN
        UPDATE [dbo].[Languages] Set IsDeleted=@IsDeleted WHERE ID=@ID
    END

我必须更改我的位置的地方.

Where I have to change my sp.

请帮助我.

答

根据您的评论

我正在从asp代码传递id参数.删除该ID的记录.

和

是的，我正在从代码传递ID.在何处更改sp，以使其适用于该参数

您的SP参数没有 @ID ，您已在本地对其进行了声明.

Your SP parameters don't have @ID, you have declared it locally.

我希望您检查是否要尝试将 @Id 作为参数传递给SP.如果是这样，则可能是错误原因，因为SP参数在参数列表中没有任何名为 @Id 的参数.

I want you to check if you are trying to passing @Idas parameter to SP. If so, it is cause of error, as SP parameters don't have any parameter named @Id in parameters list.

解决方案是在参数中添加 @Id INT = 0 之类的参数.您还必须重命名本地参数@Id&它的所有用法都会冲突.

Solution is to add parameter like @Id INT =0 in parameter. Also you you have to rename local parameter @Id & all of its usage as this can conflict.

 ALTER PROCEDURE [dbo].[hrm_Langauges]
    (
        @Name varchar(120) = 0,
        @CreatedOn datetime = 0,
        @UpdatedOn datetime = 0,
        @CreatedBy bigint = 0,
        @UpdatedBy bigint = 0,
        @IsDeleted bit = 0,
        @status as varchar(50)
        ,@Id INT =0 //Add this line
    )
    AS 
       Declare @ID_Local int;//Change

    Select @ID_Local = count(ID) + 1 from [dbo].[Languages]//change

    if(@status = 'Display')
    BEGIN
        SELECT ID FROM [dbo].[Languages] WHERE Name=@Name
    END
    else if(@status = 'Add')
    BEGIN
        IF EXISTS(SELECT Name FROM [dbo].[Languages] WHERE Name = @Name and IsDeleted=0)
    Begin
    Return 0
    End
    Else
        INSERT INTO [dbo].[Languages](Name,CreatedOn,CreatedBy) VALUES(@Name,@CreatedOn,@CreatedBy)
    END
    else if(@status = 'Update')
    BEGIN
        UPDATE [dbo].[Languages] Set Name=@Name,UpdatedOn=@UpdatedOn, UpdatedBy=@UpdatedBy WHERE ID=@ID_Local//change
    END
    else if(@status = 'Delete')
    BEGIN
        UPDATE [dbo].[Languages] Set IsDeleted=@IsDeleted WHERE ID=@ID
    END

使用存储过程进行插入，更新和删除

相关推荐