• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

Dephi ADO - Stored procedures

Hello,

I am using Delphi 2010 and SQL Server 2012.

I have an SP as follows:  It checks to see if a given user/password combination exists and returns the UserId if one is found.  It also sets the return value to 1 to indicate a user has been found.

 ALTER PROCEDURE [dbo].[DoesStandardUserExist]

	-- Add the parameters for the stored procedure here
	@Username nvarchar(50),
	@Password nvarchar(50),
	@UserId integer = null output

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  select @UserId = Users.Id from Users 
  where Users.UserId = @Username and Users.Password = @Password and Users.Status = 'A'
 -- If the login was found then return 1.  
  if not( @UserId is null) return 1 
  else return 0 ;
 
END

Open in new window

I have dropped a TADOStoreProc component on my DataModule and use it as follows:

  DoesStandardUserExist.Parameters.ParamByName('@UserName').Value := edtUserName.Text ;
  DoesStandardUserExist.Parameters.ParamByName('@Password').Value := edtPassword.Text ;
  DoesStandardUserExist.ExecProc ;

  if not VarIsNull( DoesStandardUserExist.Parameters.ParamByName('@UserId').Value ) then
  begin

Open in new window

BUT it appears that only the first time i execute the SP i get the correct return values.  After the first successful user/password match the values of @UserID and @Return_Value are always set to the last successful match.

They never get set to null (@UserId), or 0 (@Return_value)

The SP works as expected when called directly from Studio Manager.  Am i using ADOStoredProc correctly?
0
soozh
Asked:
soozh
  • 4
  • 3
3 Solutions
 
Geert GruwezOracle dbaCommented:
just to be sure you get the correct values:

use this for an intermediate test:
  if not( @UserId is null) return 100
  else return 999;

Open in new window


also reset the output parameter before executing second time:
DoesStandardUserExist.Parameters.ParamByName('@UserId').Value := Null;

Open in new window


you may be getting the number of rows affected in result
0
 
ThommyCommented:
Try a parameter refresh before setting the parameter values:

DoesStandardUserExist.Parameters.Refresh;
0
 
soozhAuthor Commented:
Hello,

Thanks for the replies.

Am i quite certain the sp returns the correct result but i will make the changes you suggest and test again.

I have tried setting the @userid parameter to null before calling but it still came back with the previous result.

This is obviously a coding error on my side but i just can see where.  Am i correct to assume that an ExecProc call should execute the SP each time it is made, and that the sp parameters should be updated correctly after the call?

I can have a look at refreshing the parameters but it does not seem to be logical ...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Geert GruwezOracle dbaCommented:
why are you using return with a procedure ????

you use return with a function
0
 
soozhAuthor Commented:
my understanding that the value of the tsql "return" statement is returned in the parameter "@Return_Value"

Maybe that is wrong?
0
 
Geert GruwezOracle dbaCommented:
you program delphi ?
same logic applies for function/procedure

return is used for indicating succes or failure inside a proc
http://msdn.microsoft.com/en-us/library/ms187926.aspx

if you create the proc as below:
ALTER PROCEDURE [dbo].[DoesStandardUserExist]

	-- Add the parameters for the stored procedure here
	@Username nvarchar(50),
	@Password nvarchar(50),
	@UserId integer = null output

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  select @UserId = Users.Id from Users 
  where Users.UserId = @Username and Users.Password = @Password and Users.Status = 'A'
END

Open in new window


does it work then ?
0
 
Geert GruwezOracle dbaCommented:
it's actually worse:
return in a proc actually generates an error

http://msdn.microsoft.com/en-us/library/ms174998.aspx
0
 
soozhAuthor Commented:
Hello,

I am pretty certain my SP works.  I am more interested in if i am calling it correctly from Delphi.

/richard
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now