Problem in Insert and Update in 1 Store Procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

Hi,

I am trying to Insert and update records in one Store procedure,My EmployeeId field is auto generated, the problem is when i use it from my aspx page it says @EmployeeID not supplied.
Code is:

SQL

create PROCEDURE usp_insertupdatepragimcrud
@EmployeeID int,
@Name varchar(50),
@Gender varchar(10),
@City varchar(50)
as begin
if not exists(select * from dbo.tblEmployeeDataAccessLayer where employeeid=@EmployeeID)
insert into tblEmployeeDataAccessLayer (Name,Gender,City) values (@Name,@Gender,@City)
else 
update tblEmployeeDataAccessLayer set Name=@Name,Gender=@Gender,City=@City where employeeid=@EmployeeID  
end

How do i fix it, as Employee id will be Auto generated.

Posted 14-Sep-13 18:29pm

Member 9176543

Add a Solution

2 solutions

Solution 2

Your stored procedure has an argument for EmployeeID, this needs to be supplied since you use it in the stored procedure to check for the employee id existing.

You should be checking if the employee exists by name instead of employee ID. Otherwise you have to pass in an employee id to the stored procedure.

Posted 14-Sep-13 18:38pm

Ron Beyer

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Parwej Ahamad · Accepted Answer · 2013-09-14T18:38:00

Solution 1

To fix your problem, mark EmployeeId as nullable so from front end if you don't pass employee id then it will work.

For better solution:

Instead checking in SP "IsExit", pass from the @EmployeeId in case you are going to Update otherwise it will be inserted.
So you need to check only
if(@Employee id is NULL)
then Insert your record
else
Update your record

Posted 14-Sep-13 18:38pm

Parwej Ahamad

Comments

Member 9176543 15-Sep-13 0:47am

Employee Id is set to is Identity,cant make it to allow nulls.

Parwej Ahamad 15-Sep-13 1:15am

It's parameter so no relation with your identity column. But yes don't try to update or insert employee id.

Member 9176543 15-Sep-13 2:01am

Did you mean this
alter PROCEDURE usp_insertupdatepragimcrud
@EmployeeID int = null,
@Name varchar(50),
@Gender varchar(10),
@City varchar(50)
as begin
if(@EmployeeID is null)
insert into tblEmployeeDataAccessLayer (Name,Gender,City) values (@Name,@Gender,@City)
else
update tblEmployeeDataAccessLayer set Name=@Name,Gender=@Gender,City=@City where employeeid=@EmployeeID
end

Parwej Ahamad 15-Sep-13 2:04am

Yes, correct

Member 9176543 15-Sep-13 2:07am

Thank You.