insert and update

future3839

Hi Guys, I am writing code for an insert part. I would like to write an store procedure which do both insert and update of my table. Imean when user call SP and pass the parameter if it was regarding insert add data otherwise update them. is it possible? here is my code

USE [Hostel]
GO
/****** Object: StoredProcedure [dbo].[SprocInsertUpdatePerson] Script Date: 10/29/2010 20:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SprocInsertUpdatePerson]
(-- Definition of Storeprocedure's parameter
@id int,
@fname nvarchar(50),
@srname nvarchar(50),
@gender nvarchar(15),
@dob datetime,
@transactionid int
)
AS

Declare @ReturnValue int
if (@id IS NULL) --New Item
Begin
set identity\_insert Person\_prs off
	insert into Person\_prs
	(
		id\_prs,
		fname\_prs,
		srname\_prs,
		gender\_prs,
		dob\_prs,
		transactionid\_prs
	)	
	values
	(
		@id,
		@fname ,
		@srname,
		@gender,
		@dob ,
		@transactionid			
	)
	
	Select @ReturnValue = Scope\_Identity()
	
End


else

Begin --Update Item
	Update Person\_prs  		
	Set 
	--Id = @id,
	fname\_prs = @fname,
	srname\_prs = @srname,
	gender\_prs = @gender,
	dob\_prs = @dob,
	transactionid\_prs = @transactionid
	where  id\_prs = @id
	
	Select @ReturnValue = @id
End


IF (@@ERROR != 0)
BEGIN
	RETURN -1
END
ELSE
BEGIN
	RETURN @ReturnValue
END

the problem is how to set the parameter for update or insert. in update I have to pass parameter to do search while in insert don't need it. however I should define my parameter. does any one has idea,please?

Rajesh Anuhya

The answer is in your code only

if (@id IS NULL) --New Item

if the @id is null the insert statement will works

else

Begin --Update Item

in else part you write for update commands..

Rajesh B --> A Poor Workman Blames His Tools <--

future3839

thnks for ur reply, but i got confused. because I will get error in passing parameter for id in update and insert.

Mycroft Holmes

What you are doing is exactly the method we use. Assumes ID is an identity primary key. Test the variable one the way in, note the isnull treatment

If IsNull(@ID,0)=0
begin

future3839 wrote:

set identity_insert Person_prs off

This is wrong, leave the identity insert alone but remove the @ID from the value list After the insert get the new identity value to pass back to the client

Set @ID = Scope_Identity
end
else
begin

Do the update Return the ID as the result

Select @ID as ID

Never underestimate the power of human stupidity RAH

future3839

hi, this is my code

USE [Hostel]
GO
/****** Object: StoredProcedure [dbo].[SprocInsertUpdatePerson] Script Date: 10/29/2010 23:54:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SprocInsertUpdatePerson]
(-- Definition of Storeprocedure's parameter
@id int,
@fname nvarchar(50),
@srname nvarchar(50),
@gender nvarchar(15),
@dob datetime,
@transactionid int
)
AS

Declare @ReturnValue int
if (@id IS NULL) --New Item
Begin
	insert into Person\_prs
	(
		--id\_prs,
		fname\_prs,
		srname\_prs,
		gender\_prs,
		dob\_prs,
		transactionid\_prs
	)	
	values
	(
		--@id,
		@fname ,
		@srname,
		@gender,
		@dob ,
		@transactionid			
	)
	
	Select @id = Scope\_Identity()--Returns the last identity value inserted into an identity column in the same scope.
	
End


else

Begin --Update Item
	Update Person\_prs  		
	Set 
	--Id = @id,
	fname\_prs = @fname,
	srname\_prs = @srname,
	gender\_prs = @gender,
	dob\_prs = @dob,
	transactionid\_prs = @transactionid
	where  id\_prs = @id
	
	Select @id as id
End


IF (@@ERROR != 0)
BEGIN
	RETURN -1
END
ELSE
BEGIN
	RETURN @ReturnValue
END

the id_prs field is autonumber. I run SP by this value

execute SprocInsertUpdatePerson 11,'XXX','YYY','male','1988/10/10',1200

select * from Person_prs

after select table nothing insert into table.while I have to add data and when pass parameter can update it. could you please help me??

Rajesh Anuhya

I think you have to pass like this for insert

execute SprocInsertUpdatePerson null,'XXX','YYY','male','1988/10/10',1200

First Parameter should be NULL

Rajesh B --> A Poor Workman Blames His Tools <--

future3839

thanks a lot. if I insert your code absolutely I don't have any problem with inserting.

execute SprocInsertUpdatePerson null,'XXX','YYY','male','1988/10/10',1200

the issue is when I am doing update who can I send parameter. for instance, I inserted 20 record by above code. now, I wanna update one of those record who have user entered id.

Update Person_prs
Set
--Id = @id,
fname_prs = @fname,
srname_prs = @srname,
gender_prs = @gender,
dob_prs = @dob,
transactionid_prs = @transactionid
where id_prs = ????????????????????????????????????????????????????????

future3839

I got it.its ok thanks a lot for your help

Mycroft Holmes

future3839 wrote:

execute SprocInsertUpdatePerson 11,'XXX','YYY','male','1988/10/10',1200

This is an update, you passed in an ID that is > 0 This will insert a record

execute SprocInsertUpdatePerson 0,'XXX','YYY','male','1988/10/10',1200

I would also return the the @ID value as the return value, it is very useful in the client.

Never underestimate the power of human stupidity RAH