Insert, Update and Delete using stored Procedure

Question

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

@ID is not a parameter for procedure hrm_Langauges.

I am trying to delete using the id column.

Here is my stored procedure.

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.

Please help me.

You should use an appropriate default value - this is NOT a good idea! @NewName varchar(120) = 0, - if you have a varchar parameter - use a varchar value as default! (not a numeric) — marc_s
– marc_s, Commented May 7, 2014 at 4:53
How you are SP calling from ASP.net? seems you are sending from front-end where as you SP is not expecting it... — Pranav Singh
– Pranav Singh, Commented May 7, 2014 at 4:58
@pranav- I am passing id parameter from asp code. Delete record for that Id. — Azhar Shahid
– Azhar Shahid, Commented May 7, 2014 at 5:00
Your SP parameters don't have @ID, you have declared it locally. In which parameter you are sending ID? — Pranav Singh
– Pranav Singh, Commented May 7, 2014 at 5:02

Pranav Singh · Accepted Answer · 2014-05-07 05:33:51Z

1

As per your comment,

I am passing id parameter from asp code. Delete record for that Id.

and

Yes I am passing Id from code. Where to change in sp so that it work for that parameter

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

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.

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

edited May 7, 2014 at 5:33

answered May 7, 2014 at 5:17

Pranav Singh

20.3k32 gold badges83 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Azhar Shahid Over a year ago

There is need to change my queries or it'll remain same ?

Azhar Shahid Over a year ago

it's working fine. But please tell me how it is working. I could not get the logic.

Pranav Singh Over a year ago

Your asp.net code was calling Stored proc with extra parameter @Id only in case of Delete that was not there, so error. What I did I provide that parameter plus I have to change local variable because it was with the same name as paramter & was used in Update. Hope it helped to get the logic. :)

Azhar Shahid Over a year ago

Thanks but for update I also need to update name field of a particular id. Is it'll work for that ?

Pranav Singh Over a year ago

let us continue this discussion in chat

|

Collectives™ on Stack Overflow

Insert, Update and Delete using stored Procedure

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related