Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
create Proc insert_Returns
@RID int output,
@ReturnsType int,
@CSPrefix varchar(50)
AS
begin
if not exists(select ReturnsType from tbl_ReturnSetings where RID=@RID)
begin 
INSERT [dbo].[tbl_ReturnSetings]
(
	[ReturnsType],
	[CSPrefix]
)
VALUES
(
	@ReturnsType,
	@CSPrefix
)
	SELECT @RID=SCOPE_IDENTITY();
	end
	else
	begin
	update tbl_ReturnSettings
	 SET	CSPrefix = @CSPrefix 
	where RID=@RID
end
end
 

Pls help me
When am insert new values above procedure working fine but the ReturnsType is exists CSprefix not updated ......
Posted 10-Jul-13 3:08am
Edited 10-Jul-13 6:10am
v5
Comments
ThePhantomUpvoter at 10-Jul-13 8:19am
   
Help you with what? This is just a code dump, not a question or description of any kind of a problem.
ryanb31 at 10-Jul-13 8:27am
   
What's your question? How can we help?
Amarender1479 at 10-Jul-13 8:31am
   
when am insert into table its working if ReturnsType is exists it is not updated
ryanb31 at 10-Jul-13 8:39am
   
I realize English may not be your first language but I cannot understand what you just said.
ryanb31 at 10-Jul-13 8:40am
   
Why is @RID set as output? Don't do that if you are wanting to use it in your where statement.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

As you have not described any details regarding the problem,i assume you want to update something after completion of insert operation.If this is so,you can make use of After Insert Trigger.Refer to below link for reference
 
Triggers in Sql Server[^]
 
Regards.. Laugh | :laugh:
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Re-indenting your code so it is clearer:
create Proc insert_Returns
@RID int output,
@ReturnsType int,
@CSPrefix varchar(50)
AS
begin
   if not exists(select ReturnsType from tbl_ReturnSetings where RID=@RID)
   begin 
      INSERT [dbo].[tbl_ReturnSetings](
	[ReturnsType],
	[CSPrefix])
      VALUES(
	@ReturnsType,
	@CSPrefix)
      SELECT @RID=SCOPE_IDENTITY();
   end
   else
   begin
      UPDATE tbl_ReturnSettings
      SET CSPrefix = @CSPrefix 
      WHERE RID=@RID
   end
end
Makes it a bit more obvious.
 
But what you are asking for is pointless - your SP does an INSERT if no record exists, or does an UPDATE if it is already present.
 
But your insert looks to use an IDENTITY field - in which case you cannot provide an ID value (So are presumably passing a negative value to ensure it does an insert). And if it does an insert, then there is no point in doing the update, because it updates the field you just inserted with the same value are are updating...
 
I think you need to sit down and think about what you are trying to do, because I'm pretty sure what you have asked for is not it...
  Permalink  
Comments
Amarender1479 at 10-Jul-13 8:38am
   
if recoed exists it is not updated
OriginalGriff at 10-Jul-13 8:55am
   
What values did you pass to the SP? how did you pass them? How did you check if the record was updated?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

why are you setting @RID output when you are using it in your where statement.
don't you think you should pass it to you SP.
 
Try below code..
 

create Proc insert_Returns
@RID int,
@ReturnsType int,
@CSPrefix varchar(50)
AS
begin
   if not exists(select ReturnsType from tbl_ReturnSetings where RID=@RID)
   begin 
      INSERT [dbo].[tbl_ReturnSetings](
	[ReturnsType],
	[CSPrefix])
      VALUES(
	@ReturnsType,
	@CSPrefix)
      SELECT @RID=SCOPE_IDENTITY();
   end
   else
   begin
      UPDATE tbl_ReturnSettings
      SET CSPrefix = @CSPrefix 
      WHERE RID=@RID
   end
end
 
I think now it should work fine for you...
  Permalink  

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



Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 10 Jul 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100