sql server update Insert statement

Question

i have a stored procedure which first of all checks for some record whether it exists or not. if already exists then executes updates statement (if update statement works fine then returns affected row no else returns a variable with -1000 value) else it selects a variable with value -2000.

Problem is it works fine if i executes it in sql server but when i call it from C# then it returns current_indent('') or only -1 no -1000 or -2000 is returned.

    USE [youfuntube]
    GO
    ALTER PROCEDURE [dbo].[usp_change_pass]     
    @e_mail varchar(60),
@old_pass varchar(27),
@new_pass varchar(27)
    AS
    BEGIN
declare @exist int;
Set @exist =0;

Select @exist=COUNT( userinfo_signup.e_mail) from userinfo_signup where userinfo_signup.e_mail=@e_mail;
if(@exist>0)
BEGIN
    SET @exist=0;
    SELECT @exist= COUNT ( userinfo_signup.e_mail) from userinfo_signup Where userinfo_signup.e_mail=@e_mail And userinfo_signup.password=@old_pass;
    if(@exist>0)
        Begin
            UPDATE userinfo_signup
            Set password=@new_pass
            Where e_mail=@e_mail AND password=@old_pass;
            Select IDENT_CURRENT( 'userinfo_signup');
        End
    ELSE
        begin
            Set @exist=-1000;
            SELECT @exist;
        end
    END
ELSE
    BEGIN
        SET @exist=-2000;
        Select @exist;
END
  END
  GO

perhaps you could add your c# code as your problem occurs there? — Pilgerstorfer Franz
– Pilgerstorfer Franz, Commented Oct 21, 2012 at 19:29
Why are you returning @exists value?? instead use an out parameter... when your update is done set your out parameter to @@ROWCOUNT or set it to -1000 or -2000... — Abdul Majid
– Abdul Majid, Commented Oct 21, 2012 at 19:32
YOu need to watch out: you're not returning the @exist as a return parameter (with a RETURN @exists statement); instead you're selecting it to an output result set. It depends on how you think you're fetching the return data (vs. result set) from your C# code ... — marc_s
– marc_s, Commented Oct 21, 2012 at 19:38
If the user is changing the password, would the user NOT already be logged in, therefore user already exists. It seems your logic could be streamlined. Also agree, looking at the C# code would be helpful — rjsteward
– rjsteward, Commented Oct 21, 2012 at 19:39

Neil · Accepted Answer · 2012-11-30 20:03:59Z

1

I would try using the MERGE command instead of an if exists. Here is a simple introduction to the command: http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

answered Nov 30, 2012 at 20:03

Neil

9197 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql server update Insert statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related