CODEWITHSUNDEEP
sql-server
Mudassar Hussain
Mudassar Hussain

Reputation: 11

Stored procedure for Delete record

I have created 3 stored procedures for inserting, updating and deleting records.

While insert and update record stored procedures are working fine, I can't delete records through the delete stored procedure.

The code in that stored procedure is here:

CREATE PROCEDURE usp_DeleteEmployeeRcord
    (@Name NVarchar(50),
     @FName Nvarchar(50),
     @Address NVarchar (50),
     @Email Nvarchar(50),
     @Mobile NVarchar(50),
     @Pincode NVarchar(50))
AS 
BEGIN
    DELETE [dbo].[EmployeeDetails]  
    WHERE Pincode = @Pincode
END

Upvotes: 0

Views: 47842

Answers (5)

Kalleshi KS
Kalleshi KS

Reputation: 9

Use test
go

CREATE TABLE employee(  
id INTEGER NOT NULL PRIMARY KEY,  
first_name VARCHAR(10),  
last_name VARCHAR(10),  
salary DECIMAL(10,2),  
city VARCHAR(20), 
)  


INSERT INTO employee VALUES (2, 'Monu', 'Rathor',4789,'Agra');  
GO  

INSERT INTO employee VALUES (4, 'Rahul' , 'Saxena', 5567,'London');  
GO  

INSERT INTO employee VALUES (5, 'prabhat', 'kumar', 4467,'Bombay'); 
go  

INSERT INTO employee VALUES (6, 'ramu', 'kksingh', 3456, 'jk');  
go  

select * from employee 


Create procedure MasterInsertUpdateDelete
(
@ID integer,  --//Mandatory parameters
@Firstname varchar(50)=Null,  --//optional parameters//--
@Lastname varchar(50)=Null,
@Salary Decimal(10, 2)=Null,
@City varchar(20)=Null,
@Statementtype nvarchar(200) = '' 
)
as
Begin

    if @Statementtype = 'Insert'
    begin
        insert into employee(id, first_name, last_name, salary, city) values(@ID, @Firstname, @Lastname, @Salary, @City)
    end

    if @Statementtype = 'Select'
    begin
            select *
        from employee
    end
    if @Statementtype = 'Update'
    begin
        update employee set first_name = @Firstname, last_name=@Lastname, salary=@Salary, city=@City
        where id=@ID
    end

    if @Statementtype = 'Delete' 
    begin
    delete from  employee where id=@ID
    end
end

--For insert the records
exec MasterInsertUpdateDelete 7, 'Kallesh', 'Yadav', 30000, 'Bangalore', 'Insert'


--For update the records
exec MasterInsertUpdateDelete 4,'Sneha', 'Kumari', 23000, 'Mumbai', 'Update'

select *
from employee

--For delete the records
exec MasterInsertUpdateDelete  @ID = 2,  @Statementtype = 'Delete' -->We should specify the parameters name while deleting the specific records

Hope it helps. :)

Upvotes: 0

Vaibhav_Welcomes_You
Vaibhav_Welcomes_You

Reputation: 961

Stored Procedure for Delete Record - Example of Book Details as Follow:

    Create procedure[dbo].[DeleteBookDetails_SP] 
         @BookId Int
    As
    Begin   

         Delete from BookDetails Where BookId=@BookId

    End

Here "DeleteBookDetails_SP" is Stored Procedure Name , "BookDetails" is Table Name and "BookId" is Column Name in Table.

Upvotes: 0

Sachin
Sachin

Reputation: 40990

Because your DELETE statement is not correct. Correct syntax is 

DELETE FROM <TableName> Where <Condition>

So change your query to this and then try

DELETE FROM [dbo].[EmployeeDetails] Where Pincode = @Pincode

Upvotes: 2

Kevin
Kevin

Reputation: 781

You forgot the FROM clause in your statement, it's 

DELETE FROM dbo.EmployeeDetails WHERE ....

Upvotes: 2

Dheeraj Sharma
Dheeraj Sharma

Reputation: 709

You have to make variables optional which you are not passing in the procedure:

Use this modified:

CREATE PROCEDURE usp_DeleteEmployeeRcord
(
 @Name NVarchar(50)='',
 @FName Nvarchar(50)='', 
 @Address NVarchar (50)='', 
 @Email Nvarchar(50)='', 
 @Mobile NVarchar(50)='', 
 @Pincode NVArchar(50)
)

AS 
        BEGIN
            DELETE [dbo].[EmployeeDetails]  
            Where Pincode = @Pincode
        END

Hope it helps. :)

Upvotes: 0

Related Questions