Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 C# , +
Table script as ---

CREATE TABLE [dbo].[JS_ResumeInsert](
        [JS_Id] [int] NULL,
        [UserEmailId] [varchar](150) NULL,
        [Name] [varchar](50) NULL,
        [Phone] [varchar](200) NULL,
        [Institute] [varchar](100) NULL,
        [Batch] [varchar](50) NULL,
        [Designation] [varchar](100) NULL,
        [Organization] [varchar](100) NULL,
        [Experience_Months] [varchar](10) NULL,
        [Experience_Years] [varchar](10) NULL,
        [TotalExp] [int] NULL,
        [Industry] [varchar](300) NULL,
        [CurrentLocation] [varchar](100) NULL,
        [PreferredLocation] [varchar](100) NULL,
        [IsInserted] [int] NULL,
        [IsUpdated] [int] NULL,
        [CreationDate] [datetime] NULL
    )



storedprocedure as---


CREATE PROCEDURE JSP_ResumeUpdate  
       
     @JS_Id [int],  
     @UserEmailId [varchar](150),  
     @Name [varchar](50),  
     @Phone [varchar](200) ,  
     @Institute [varchar](100) ,  
     @Batch varchar(50),  
     @Designation varchar(100),  
     @Organization varchar(100) ,  
     @Experience_Months varchar(10),  
     @Experience_Years varchar(10) ,  
     @TotalExp int ,  
     @Industry varchar(300),  
     @CurrentLocation varchar(100) ,  
     @PreferredLocation varchar(100) ,  
     @IsInserted int ,  
     @IsUpdated int ,   
     @Result Tinyint OutPut      
          
    AS              
     BEGIN                    
          
          IF EXISTS(SELECT  * FROM  JS_ResumeInsert WHERE UserEmailId =@UserEmailId )                  
     BEGIN                    
       SET @Result = 0-- Already Exists                    
     END                    
               
     ELSE                    
      BEGIN     
          
       
      UPDATE  JS_ResumeInsert SET    
       
         JS_Id=@JS_Id ,  
         UserEmailId=@UserEmailId ,  
      Name=@Name ,  
      Phone=@Phone ,  
      Institute=@Institute  ,  
      Batch=@Batch ,  
      Designation=@Designation ,  
      Organization=@Organization ,  
      Experience_Months=@Experience_Months,  
      Experience_Years=@Experience_Years ,  
      TotalExp=@TotalExp ,  
      Industry=@Industry ,  
      CurrentLocation=@CurrentLocation ,  
      PreferredLocation=@PreferredLocation  ,  
      IsInserted=@IsInserted ,  
      IsUpdated=@IsUpdated ,  
      CreationDate=GETDATE()  
          
       
     set @Result =1                 
     return      
        
     END  
    END
Posted 25-Jan-13 1:48am
sr_24484
Edited 25-Jan-13 2:51am
MT_10.2K
v2
Comments
Zoltán Zörgő at 25-Jan-13 6:50am
   
1) What do you want exactly to achieve?
2) What is the issue precisely?
subodhrastogi24 at 25-Jan-13 6:52am
   
i already have data in table JS_ResumeInsert ,but the stored procedure is not updating it
subodhrastogi24 at 25-Jan-13 6:55am
   
it is generating no error but no updation as well
Zoltán Zörgő at 25-Jan-13 7:03am
   
Have you tried to debug the sp from within SQL Management Studio?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Well..well,
 
You are saying you have data in the table so naturally the first condition in your SP will be true
 
Your update statement is in ELSE, so it will never execute.
 
Second and very important point, you do not have "where" clause in the UPDATE statement, this will update ALLThe rows of the table with the data passed.
 
Hoep that helps. If it does, mark it as answer/upvote.
Milind
  Permalink  
Comments
subodhrastogi24 at 25-Jan-13 7:38am
   
thanks MIlind_T it has updated all rows without where clause
 
I have changed my sp to this one,
what should i write in WHERE clause so that a record with a particular EMAiL and JS_ID will be updated
 

 
CREATE PROCEDURE JSP_ResumeUpdate

@JS_Id [int],
@UserEmailId [varchar](150),
@Name [varchar](50),
@Phone [varchar](200) ,
@Institute [varchar](100) ,
@Batch varchar(50),
@Designation varchar(100),
@Organization varchar(100) ,
@Experience_Months varchar(10),
@Experience_Years varchar(10) ,
@TotalExp int ,
@Industry varchar(300),
@CurrentLocation varchar(100) ,
@PreferredLocation varchar(100) ,
@IsInserted int ,
@IsUpdated int ,
@Result Tinyint OutPut

AS
BEGIN

-- IF EXISTS(SELECT * FROM JS_ResumeInsert WHERE UserEmailId =@UserEmailId )
--BEGIN
-- SET @Result = 0-- Already Exists
--END

--ELSE
-- BEGIN


UPDATE JS_ResumeInsert SET
JS_Id=@JS_Id ,
UserEmailId=@UserEmailId ,
Name=@Name ,
Phone=@Phone ,
Institute=@Institute ,
Batch=@Batch ,
Designation=@Designation ,
Organization=@Organization ,
Experience_Months=@Experience_Months,
Experience_Years=@Experience_Years ,
TotalExp=@TotalExp ,
Industry=@Industry ,
CurrentLocation=@CurrentLocation ,
PreferredLocation=@PreferredLocation ,
IsInserted=@IsInserted ,
IsUpdated=@IsUpdated ,
CreationDate=GETDATE() where UserEmailId=@UserEmailId


set @Result =1
return

--END
END
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi..
 
What syntax are you using to execute this procedure??
 
My best guess is that you have created your procedure, but you haven't executed it.
 
If you have, please post its code, there might be some error in that.
 
ALTER PROCEDURE dbo.Test @ip  VARCHAR(10),
                       @op VARCHAR(20) output
 
DECLARE @x VARCHAR(20);
 
EXEC dbo.Test "Test", @op output
 
SELECT @op
 
Hope it helps...
 
Cheers
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,382


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 25 Jan 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