Hi all, I have a web page and I am trying to call a stored proc and have it return a value of 0 or 1 to me. I am having a problem getting this to work, can someone please tell me what I am doing wrong on this... Please see attached.
Thanks for any and all help
Here is my code-behind that is calling the storedproc, but not sure what needs to be modified to receive a value back...
Using conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("WebConnectionString").ConnectionString)
Dim cmd As New SqlCommand("Approved_User", conn)
cmd.Parameters.Add(New SqlParameter("@Login", SqlDbType.VarChar, 20))
cmd.Parameters("@Login").Value = hdnUser.Value
Dim paramReturnValue As New SqlParameter()
paramReturnValue.ParameterName = "@return_value"
paramReturnValue.SqlDbType = SqlDbType.Int
paramReturnValue.Direction = ParameterDirection.ReturnValue
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandTimeout = 300
Here is my SQL, again not sure what I need to change to get it to send a value back once it has been called, I need something like the following, I am trying to see if a person is on a list of approved users and return a 1 if they are or a 0 if they are not.
ALTER PROCEDURE [dbo].[ApprovedUser]
DECLARE @rc Int
SELECT distinct Security_User.USERID
inner join UserBranch on Security_User.UserID = UserBranch.UserID
inner join UserInfo on Security_User.UserID = UserInfo.UserID
inner join Security_User_Role on Security_User.UserID = Security_User_Role.UserID
WHERE Login = @Login and IsActive = 1 and (BranchRoleCode in (Select distinct BranchRoleCode from BranchRole)
or IsGlobalOverrider = 1 or RoleID = 1)
IF @@RowCount > 0
SELECT @rc = 1
SELECT @rc = 0