how to insert two diffrent table in stored procedure sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi All

Here i am facing problem with insert two table in store procedure

my Code is:

SQL

create proc [dbo].[Insert_StaffMaster_DeviceInfo_SP]
(
@UserIdN int,
@EmpCodeC nvarchar(15),
@empnamec varchar(50),
@deptcodec varchar(50),
@DesigCodeC varchar(50),
@seccodec varchar(50),
@Dojd smalldatetime,
@TerminateDateD smalldatetime,
@DOB smalldatetime,@CatgCodeC varchar(50),
@Gender nvarchar(1),@Photo nvarchar(255),
@LocationC nvarchar(150),@ShiftGroupCodeC varchar(10),
@NricWPNoC varchar(15),
@Privilege varchar(50),
@Password nvarchar(50),
@Enabled nvarchar(50),
@CardNumber nvarchar(MAX) 
)
as
begin
if not exists(select UserIdN from StaffMaster where UserIdN=@UserIdN)
insert into StaffMaster (UserIdN,EmpCodeC,empnamec,deptcodec,DesigCodeC,seccodec,Dojd,TerminateDateD,DOB,CatgCodeC,Gender,Photo,LocationC,ShiftGroupCodeC,NricWPNoC) values (@UserIdN,@EmpCodeC,@empnamec,@deptcodec,@DesigCodeC,@seccodec,@Dojd,@TerminateDateD,@DOB,@CatgCodeC,@Gender,@Photo,@LocationC,@ShiftGroupCodeC,@NricWPNoC)
insert into  Device_StaffFP(UserIDN,Privilege,Password,Enabled,CardNumber) values (@UserIDN,@Privilege,@Password,@Enabled,@CardNumber)
else 
update StaffMaster set UserIdN=@UserIdN,EmpCodeC=@EmpCodeC,empnamec=@empnamec,deptcodec=@deptcodec,DesigCodeC=@DesigCodeC,seccodec=@seccodec,Dojd=@Dojd,TerminateDateD=@TerminateDateD,DOB=@DOB,CatgCodeC=@CatgCodeC,Gender=@Gender,Photo=@Photo,LocationC=@LocationC,ShiftGroupCodeC=@ShiftGroupCodeC,NricWPNoC=@NricWPNoC where UserIdN=@UserIdN
update Device_StaffFP set UserIDN=@UserIDN,Privilege=@Privilege,Password=@Password,Enabled=@Enabled,CardNumber=@CardNumber where UserIDN=@UserIDN
end

when i try this i got error that is,

Incorrect syntax near the keyword 'else'

.

and one more this shall i use this query for Updating ?

Thank You !

Posted 11-Aug-14 3:44am

prasanna.raj

Add a Solution

1 solution

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)

Gupta Poonam · Answer 1 · 2014-08-11T03:58:00

Solution 1

your if block contains more than 1 statement so you have to wrap them between BEGIN....END

IF not exists(....)
BEGIN

Insert statement 1
Insert statement 2
END
ELSE
BEGIN
update statement 1
update statement 2
END

Posted 11-Aug-14 3:58am

Gupta Poonam

Comments

prasanna.raj 13-Aug-14 2:03am

i Got same error

Incorrect syntax near the keyword 'else' ...

Gupta Poonam 13-Aug-14 5:46am

i have tried it on sample tables... you can check the below code its working.

create proc [dbo].[CommentsInsertTest]
(
@CommentID INT,
@ParaID INT,
@Comment VARCHAR(MAX),
@CreatedOn DATETIME,
@EditedOn DATETIME,
@CreatedBy INT,
@EditedBy INT,
@IsActive BIT
)
as

begin

if not exists(select CommentID from Comments where CommentID=@CommentID)
BEGIN
insert into Comments(ParaID,Comment,CreatedOn,EditedOn,CreatedBy,EditedBy,IsActive) values (@ParaID,@Comment,@CreatedOn,@EditedOn,@CreatedBy,@EditedBy,@IsActive)
insert into AdditionalComment(Comment,CreatedBy,ModifiedBy) values (@Comment,@CreatedBy,@EditedBy)
END
else
BEGIN
update Comments set Comment=@Comment,EditedOn=@EditedOn,EditedBy=@EditedBy where CommentID=@CommentID
update AdditionalComment set Comment=@Comment,ModifiedBy=@EditedBy where AddCommentID=@CommentID
END
end

prasanna.raj 14-Aug-14 3:02am

sure i ll try..