Update storedprocedure not updating table data

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

C#

SQL-Server

, +

Table script as ---

SQL

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---

SQL

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 0:48am

sr_24

Updated 25-Jan-13 1:51am

MT_

v2

Add a Solution

Comments

Zoltán Zörgő 25-Jan-13 6:50am

1) What do you want exactly to achieve?
2) What is the issue precisely?

sr_24 25-Jan-13 6:52am

i already have data in table JS_ResumeInsert ,but the stored procedure is not updating it

sr_24 25-Jan-13 6:55am

it is generating no error but no updation as well

Zoltán Zörgő 25-Jan-13 7:03am

Have you tried to debug the sp from within SQL Management Studio?

2 solutions

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.

SQL

ALTER PROCEDURE dbo.Test @ip  VARCHAR(10),
                       @op VARCHAR(20) output

SQL

DECLARE @x VARCHAR(20);

EXEC dbo.Test "Test", @op output
 
SELECT @op

Hope it helps...

Cheers

Posted 25-Jan-13 1:05am

Anurag Sinha V

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)

MT_ · Accepted Answer · 2013-01-25T01:08:00

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

Posted 25-Jan-13 1:08am

MT_

Comments

sr_24 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