Wednesday, March 28, 2012

How to get the returning value of a stored procedure?

Hi everyone!
I am new to sql server 2005 and visual studio 2005.

I have the following simple stored procedure that checks if a user exists:
-------------------------------
ALTER PROCEDURE [dbo].[sp_Users_AlreadyExists]

@.UserName varchar(256)
AS
BEGIN
SET NOCOUNT ON;


IF (EXISTS (SELECT UserName FROM dbo.Users WHERE LOWER(@.UserName) = LoweredUserName ))
RETURN(1)
ELSE
RETURN(0)
END
-------------------------------

I use the following code to execute the procedure on visual studio:
-------------------------------
.
.
.
cmdobj As SqlCommand
cmdobj = New SqlCommand(sp_Users_AlreadyExists, connobj)
cmdobj.CommandType = CommandType.StoredProcedure
cmdobj.Parameters.AddWithValue("@.UserName", "blablalala")
cmdobj.ExecuteNonQuery()
cmdobj.Dispose()
connobj.Close()
.
.
.
-------------------------------

I expected that cmdobj.ExecuteNonQuery() would return 1 if the userblablab exists or 0 if the user doesnt, but it just return -1 (i thinkbecause no row was affected)

Does anyone knows how to retrieve the value that my stored procedure returns?

Thanx in advance!

cmdobj.parameters.add("@.RETURN_VALUE",sqldbtype.int).direction=returnvalue

Then check the parameter value after the execute like:

dim val as integer=cmdobj.parameters("@.RETURN_VALUE").value

sql

No comments:

Post a Comment