Stored Procedure with identity column

Jaya Prakash

This is my db

how to Write stored procedure for edit when im taking identity column pls help me

create table mytable(id int identity(1,1),fname varchar(55),lname varchar(55),mobno bigint,Email varchar(100),salary money)

create procedure sp_GridData
as
begin
select * from mytable
end

create procedure sp_Insert
(
 @fname varchar(55),@lname varchar(55),@mobno bigint,@Email varchar(100),@salary money
)
as begin
insert into mytable values(@fname,@lname,@mobno,@Email,@salary)
end

create procedure sp_Edit
(
@fname varchar(55),@lname varchar(55),@mobno bigint,@Email varchar(100),@salary money
)
update mytable set fname=@fname,lname=@lname,mobno=@mobno,Email=@Email,salary=@salary   (Here How To Write Where Condition )

end

And also how to write sp for delete

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Aradhana Tripathi3y ago

Accepted answer

Hello Jaya,

Use below stored proc which insert\update\delete row using identity column.

--SP for insert\update using identity column 
--declare @id parameter as NULL. if it is null then insert else update using indentity column.

CREATE PROCEDURE sp_Edit
(
  @id INT = NULL,
  @fname VARCHAR(55),
  @lname VARCHAR(55),
  @mobno BIGINT,
  @Email VARCHAR(100),
  @salary MONEY
)
AS
BEGIN
	IF @id IS NULL
    BEGIN
        INSERT mytable
        VALUES (@fname,@lname,@mobno,@Email,@salary)
    END
    ELSE
	BEGIN
        UPDATE mytable SET
		fname=@fname, lname=@lname, mobno=@mobno, Email=@Email, salary=@salary
		WHERE id=@id
    END

END
GO

--SP for delete using identity column

CREATE PROCEDURE sp_Delete
(
  @id INT
)
AS
BEGIN
  DELETE FROM mytable
  WHERE id = @id
END

Rajeev Kumar3y ago
create procedure usp_Edit ( @fname varchar(55),@lname varchar(55),@mobno bigint,@Email varchar(100),@salary money ) as Begin

update mytable set fname=@fname,lname=@lname,mobno=@mobno,Email=@Email,salary=@salary WHERE id = @id end

Naimish Makwana3y ago

Hello Jaya,

For update, use below sp, It will update record by Id.

CREATE PROCEDURE sp_Edit
(
  @id INT,
  @fname VARCHAR(55),
  @lname VARCHAR(55),
  @mobno BIGINT,
  @Email VARCHAR(100),
  @salary MONEY
)
AS
BEGIN
  UPDATE mytable
  SET fname = @fname, lname = @lname, mobno = @mobno, Email = @Email, salary = @salary
  WHERE id = @id
END

For Delete by Id:

CREATE PROCEDURE sp_Delete
(
  @id INT
)
AS
BEGIN
  DELETE FROM mytable
  WHERE id = @id
END

Thanks

Naimish Makwana