Stored Procedure help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Stored Procedure help

Author

Topic

KellyPro216
Starting Member

32 Posts

Posted - 2004-07-22 : 16:30:15

Here is my stored procedure that is now a mess. Before I created the RegString, I had @var1 and @var2 as outut parameters, one would diplay -1 or -2 if the username and/or password were not correct and @var1 would display the userID. Now, i have been told that i should just create one output paramter in my stored procedure, and it should output @var1 if it is correct and @var2 if @var1 is null. How can i fix this code to get this to work?


CREATE PROCEDURE DBAuthenticate
( 
   @login nVarChar (20), 
   @pw nVarChar (20), 
   @var1 varchar(4) ,
   @var2 INt,
   @Retstring as varchar(10) OUTPUT
) 

AS 
SET NoCOUNT ON
Declare @Actualpw nVarchar (20) 

Select @var1 = uservar1, @Actualpw = regpw 
From dbo.users
Where user = @login 

If @var1 is not null 
Begin
	if @pw <> @actualpw 
		SET @var2 = -2
End
Else 
	SET @var2 = -1

SET @RetString = @var1
SET @RetString = IsNull(@var1,@var2) 

RETURN
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-22 : 16:44:32

cusoxty?

I'm confused. So you need to @Retstring to something based on something. I don't understand the @var, @var2 thing. Just set @Retstring to whatever it needs to be.

Tara

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-22 : 23:19:10

Your code sets @var2 to -2 if the password is not correct, but This procedure will never return the -2 because in order to get to this line @var1 cannot be null and ur last line says if @var is not null return @var2 9which will never happen when @var2 = -2) Did that make sense? Here's what I'd do:

CREATE PROCEDURE DBAuthenticate
( 
   @login nVarChar (20), 
   @pw nVarChar (20), 
   @var1 varchar(4) ,
--   @var2 INt,
   @Retstring as varchar(10) OUTPUT
) 

AS 
SET NoCOUNT ON
Declare @Actualpw nVarchar (20) 

Select @var1 = uservar1, @Actualpw = regpw 
From dbo.users
Where user = @login 

If @var1 is not null 
Begin
	if @pw <> @actualpw 
		SET @var1 = -2
End
Else 
	SET @var1 = -1

SET @RetString = @var1
--don't need this next line
--SET @RetString = IsNull(@var1,@var2) 


RETURN
GO

- RoLY roLLs

KellyPro216
Starting Member

32 Posts

Posted - 2004-07-26 : 10:06:20

Hello. I am still having a problem recognizing my -1 case. When the user enters a wrong ID, it says 'Invalid password'. It also correctly says 'invalid password, when the username is correct and the password is wrong. Finally, if the username and password are a match, it brings the user ot the next page, as it should. So how is my -1 case going unnoticed? It seems to work fine in query analyzer.


Sub button_Click (s As Object, e as EventArgs) 
If IsValid Then 
Dim IntUserID as String 
IntUserID = DBAuthenticate(txtusername.text, txtpassword.text) 
Select Case IntUserID 
Case -1
LblMessage.text = "Non existent user" 
Case -2 
LblMessage.text = "Invalid Password" 
Case Else 
Session("Regionid") = IntUserID 
Session("RegionName") = TxtUsername.text 
response.redirect(return_page) 
End Select 
end If 
End Sub

Sub button_Click2 (s As Object, e as EventArgs)
	response.redirect(back_page)

End Sub

Function DBAuthenticate (strUsername As String, strPassword As String) As String

Dim conMyData As SqlConnection 
Dim cmdSelect As SqlCommand 
Dim ParmReturnValue As SqlParameter 

conMyData = New SqlConnection("Server=localhost;UID=--;PWD=--;Database=--") 
cmdSelect = New SqlCommand ("DbAuthenticate", conMyData) 
cmdSelect.CommandType = CommandType.StoredProcedure 
cmdSelect.Parameters.Add( "@retstring", SqlDBType.varchar, 4).Direction = ParameterDirection.Output 
cmdSelect.Parameters.add("@login", strusername) 
cmdSelect.Parameters.add("@pw", strpassword) 

conMyData.Open() 

cmdSelect.ExecuteNonQuery() 

conMyData.Close() 
Dim result as string = CStr(cmdSelect.Parameters("@retstring").value) 

Return result 

End Function

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-26 : 10:42:21

What happens if @var1 isn't null, but just an empty string, as it could be..
What happens if the SELECT statement doesn't return anything ?
Why is @var1 in the parameter list in the sproc, but not in your ASP ? You don't need @var1 in param list as far as I can see.


CREATE PROCEDURE DBAuthenticate
( 
   @login nVarChar (20), 
   @pw nVarChar (20), 
--   @var1 varchar(4) ,
--   @var2 INt,
   @Retstring as varchar(10) OUTPUT
) 

AS 
SET NoCOUNT ON
Declare @Actualpw nVarchar (20) 
DECLARE @var1 VARCHAR(4)

Select @var1 = uservar1, @Actualpw = regpw 
From dbo.users
Where user = @login 
IF @@ROWCOUNT=0 BEGIN
    SET @var1=NULL
END

If @var1 is not null 
Begin
	if @pw <> @actualpw 
		SET @var1 = -2
End
Else 
	SET @var1 = -1

SET @RetString = @var1
--don't need this next line
--SET @RetString = IsNull(@var1,@var2) 


RETURN
GO

KellyPro216
Starting Member

32 Posts

Posted - 2004-07-26 : 11:36:16

Yeh... I had it set up in my asp so @var1 was = to an empty string. The only value i need on my application is the @regstring output parameter. This gets its value from @var1 though. So if i declare @var1 where you did, do i need to declare it in the asp?

KellyPro216
Starting Member

32 Posts

Posted - 2004-07-26 : 11:39:29

i think i got it to work. Thank you for the advice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources