stored procedure result comparision

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

Curt_C [MVP]

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

Viktor Popov

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

Viktor Popov

It doesn't work with SqlAdapter and SqlCommand.
Could you explain how exactly should I do that?
Thanks
 
E

Eliyahu Goldin

In SqlCommand (or similar class for other databases) setup a parameter with
property Direction set to ReturnValue.

Eliyahu
 
V

Viktor Popov

I've read it but these SPs return rowsets. My SP return a INT. Is it the
same way of implementing?
 
E

Eliyahu Goldin

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
 
B

bruce barker

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

Viktor Popov

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SP and C# problem 3
ASP.NET/C#->MS SQL help 2
Help with ASP.NET/ C# -> MSSQL 2
ASP.NET/C# Help 1

Top