December 22, 2003 at 5:01 am
Hi there,
I wonder if you guys can help me with this. I have the following SP:
CREATE PROCEDURE usp_check_login
@username varchar(20),
@password varchar(20)
AS
DECLARE @user_exists int
DECLARE @user_id int
DECLARE @login_active int
DECLARE @login_attempts int
DECLARE @user_type int
SET NOCOUNT ON
SELECT @user_exists = COUNT(*)
FROM tbl_user
WHERE user_username = @username
SELECT @user_id = user_id, @login_active = user_active, @login_attempts = user_loginattempts, @user_type = user_type
FROM tbl_user
WHERE user_username = @username
AND user_password = @password
SELECT @user_exists AS 'user_exists',
@user_id AS 'user_id',
@login_active AS 'login_active',
@login_attempts AS 'login_attempts',
@user_type AS 'user_type'
SET NOCOUNT OFF
GO
If I execute this in the Query Analyzer it works fine. However, using Visual Basic with the ADODB.Command.Refresh method, there are aparantly 3 parameters? The first is "@RETURN_VALUE" and the other two are "@username" & "@password".
Why and where is this "@RETURN_VALUE" parameter coming from?
Thanks in advance...
December 22, 2003 at 5:28 am
It is the RETURN value of the stored procedure. Every SQL Server stored procedure has this parameter (whether it is explicitly used or not).
Far away is close at hand in the images of elsewhere.
Anon.
December 22, 2003 at 7:35 am
OK... Cheers.
I need to do a bit more homework on this SQL Server T-SQL!
Edited by - jkeepi on 12/22/2003 07:41:52 AM
December 22, 2003 at 7:40 am
quote:
OK... Does that mean that if I create a basic SP with an insert I will still always be returned this "@RETURN_VALUE" parameter?
Yep
* Noel
December 23, 2003 at 1:59 am
You could have coded your SP as follows
CREATE PROCEDURE usp_check_login
@username varchar(20),
@password varchar(20) AS
SET NOCOUNT ON
SELECT user_id,
user_active as login_active,
user_loginattempts as login_attempts,
user_type
FROM tbl_user
WHERE user_username = @usernameAND user_password = @password
RETURN @@ROWCOUNT
GO
That way you could use your RETURN_VALUE to determine whether or not your user exists i.e. if the value is >0 then the user and password combination exists.
If possible I would avoid using the ADO Command.Refresh statement because it has a noticeable performance hit.
Try declaring the parameters explicitly
Dim lResult as long
Dim cmdLogin As ADODB.Command
dim rsLogin as ADODB.Recordset
Set cmdLogin = New ADODB.Command
cmdLogin .ActiveConnection = connString
cmdLogin .CommandText = "usp_check_login"
cmdLogin .CommandType = 4
cmdCommunity.CommandTimeout = 0
cmdLogin .Parameters.Append cmdCommunity.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)
cmdLogin .Parameters.Append cmdCommunity.CreateParameter("@username", adVarChar, adParamInput, 20, sUsername)
cmdLogin .Parameters.Append cmdCommunity.CreateParameter("@password", adVarChar, adParamInput, 20, sPassword)
set rsLogin = cmdLogin .Execute
lResult = cmdLogin .Parameters(0).Value
etc
========================
He was not wholly unware of the potential lack of insignificance.
December 23, 2003 at 2:22 am
quote:
If possible I would avoid using the ADO Command.Refresh statement because it has a noticeable performance hit.
True but by default ADO will fill the parameter list automatically anyway unless (I believe) you explicitly tell it not to do so.
p.s. This is not the same in ADO.NET, in .NET the parameter list is not filled.
Edited by - davidburrows on 12/23/2003 02:23:05 AM
Far away is close at hand in the images of elsewhere.
Anon.
December 28, 2003 at 6:27 pm
Cheers for the feedback guys...
David.Poole - I would normally always avoid using "Command.Refresh" but due to the quick turn-around on this project I have had to develop a generic DAL which can call any SP. SQL Server and the VB DLLs will be hosted on the same machine so the extra call to the DB should not slow things down too much (I hope!).
January 5, 2004 at 1:55 am
Have you noticed that when you write an ActiveX DLL, passing values ByRef is faster than ByVal, but when you write an ActiveX EXE the opposite is true?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply