stored procedure result comparision

  • Thread starter Thread starter Viktor Popov
  • Start date Start date
V

Viktor Popov

Hi ,
I have the following SP:
CREATE PROCEDURE prSVUSERINFO
@USRNAM VARCHAR(20),
@USRPSW VARCHAR(20),
@NAME VARCHAR(64),
@ADDR VARCHAR(74),
@EML VARCHAR(64),
@ROLE VARCHAR(6),
@RCOUNT INT OUTPUT
AS
IF NOT EXISTS( SELECT * FROM BLEK.USERS A WHERE A.USERNAME=@USRNAM OR
A.USEREMAIL=@EML)
BEGIN
INSERT INTO
ESTATE.BLEK.USERS(Username,UserPass,RealName,UserAddr,UserEmail,UserRole)
VALUES(@USRNAM,@USRPSW,@NAME,@ADDR,@EML,@ROLE)
END
ELSE
RETURN SELECT @RCOUNT=@@ROWCOUNT

Could I use somehow @RCOUNT in ASP.NET/C# to find out if the username
is already in use or not?

Thank you!
 
yes, you should be able to get the paramaters("@Ident").value
that's not exact syntax but should get you close.
 
Thanks for the reply!

This is my if statement

if(sqlAdapter.SelectCommand.Parameters["@RCOUNT"].Value == 0) , but there
is an error:

Operator '==' cannot be applied to operands of type object type 'object'
and 'int'.



I know that I'm close but.....

Could you help here?



Thanks!
 
It doesn't work with SqlAdapter and SqlCommand.
Could you explain how exactly should I do that?
Thanks
 
In SqlCommand (or similar class for other databases) setup a parameter with
property Direction set to ReturnValue.

Eliyahu
 
I've read it but these SPs return rowsets. My SP return a INT. Is it the
same way of implementing?
 
Are you replying to my message? If you are, look at the MSDN help topic
"Using Stored Procedures with a Command" . It includes an example of using
ReturnValue.

Eliyahu
 
yes, look at sqlcommand. besure to decalre the parameter as an out. also the
values will not be set until after all result sets. a common approach is to
close the connection

air code:

SqlCommand cmd = new SqlCommand("prSVUSERINFO",new
SqlConnection(connectString));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@USRNAM,SqlDbType.VarChar,20).Value = username;
....

cmd.Parameters.Add("@RCOUNT ,SqlDbType.Int,4).Direction =
ParameterDirection.Output;
cmd.ExecuteNonQuery();
int rCount = cmd.Parameters["@RCOUNT"].Value as int;


of course the way your sp is written rCount will allways be 0

-- bruce (sqlwork.com)
 
Thanks for the answer, Bruce!
I have changed the SP, I didn't know that the value of @RCOUNT is everytime
0.I thought that " if EXISTS( select * from ....)" changes the value of
@@ROWCOUNT...
I rewrite it this way:


CREATE PROCEDURE prSVUSERINFO
@USRNAM VARCHAR(20),
@USRPSW VARCHAR(20),
@NAME VARCHAR(64),
@ADDR VARCHAR(74),
@EML VARCHAR(64),
@ROLE VARCHAR(6),
@FLAG INT OUTPUT

AS

IF NOT EXISTS( SELECT * FROM BLEK.USERS A WHERE A.USERNAME=@USRNAM OR
A.USEREMAIL=@EML)
BEGIN
INSERT INTO
ESTATE.BLEK.USERS(Username,UserPass,RealName,UserAddr,UserEmail,UserRole)
VALUES(@USRNAM,@USRPSW,@NAME,@ADDR,@EML,@ROLE)
SET @FLAG=0
RETURN @FLAG
END
ELSE
BEGIN
SET @FLAG=1
RETURN @FLAG
END

and I check if rCount ==0{ some code}
else{
some code}
Now everything works. Could you check the SP and if I could optimize it
somehow, please tell me:)
Do you know how I could do that check using @@ROWCOUNT?


Thank you very much!

Viktor
 
Back
Top