sql server programming: IF EXISTS? -- sql server programming

Re: IF EXISTS?

Arnie Rowland
9/24/2006 12:19:01 AM

You can even simplify the process a bit more. (I recommend AGAINST using =
the RETURN value to return DATA to the application. It 'should' be used =
as a status indicator, returning success or failure codes.)

CREATE PROCEDURE dbo.LoginUser
( @UserName varchar(50),
@Password varchar(50)
)
AS
DECLARE @ID int

SELECT @ID =3D isnull( UserID , 0 )
FROM Users=20
WHERE ( UserName =3D @UserName
AND Password =3D @Password
)
=20
SELECT isnull( @id, 0 )
GO

--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous

[quoted text, click to view]

Re: IF EXISTS?

Tom Cooper
9/24/2006 1:29:11 AM

Are you sure it's returning zero. I would think it would return NULL. You
can't use the query inside and IF EXISTS () to set a vairable in this way.
And you you don't need to. Just use a select to set @ID and then you can
test @@ROWCOUNT to see if you found a row. If you didn't, set @ID to 0,
something like:

ALTER PROCEDURE dbo.LoginUser
@UserName varchar(50),
@Password varchar(50)
AS
DECLARE
@ID int

SELECT @ID = UserID FROM Users WHERE UserName = @UserName
AND Password = @Password
IF @@ROWCOUNT = 0
BEGIN
SET @ID = 0
END
RETURN @ID

[quoted text, click to view]

Re: IF EXISTS?

RON
9/24/2006 5:50:42 AM

Arnie, since you recommend not to use RETURN values, how would I pass
the UserID of the user who has logged in using his UserName & Password,
to, say, an ASP.NET page? This is the ASP.NET code:

Sub btnSubmit(ByVal obj As Object, ByVal ea As EventArgs)
Dim sqlCmd As SqlCommand
Dim sqlConn As SqlConnection

sqlConn = New SqlConnection(".....")
sqlCmd = New SqlCommand("LoginUsers", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

With sqlCmd
.Parameters.Add("@ID", SqlDbType.Int, 4).Direction =
ParameterDirection.ReturnValue
.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value =
txtUserName.Text
.Parameters.Add("@Password", SqlDbType.VarChar, 50).Value =
txtPassword.Text
End With

sqlConn.Open()
sqlCmd.ExecuteNonQuery()

If (sqlCmd.Parameters(0).Value = 0) Then
Response.Write("Invalid User")
End If

sqlConn.Close()
End Sub

If a user happens to be a valid user, then the return value will always
be greater than 0. Now since you recommend against using RETURN values,
how do I modify the stored procedure to use an OUTPUT parameter & then
call the stored procedure from the ASP.NET page so that the OUTPUT
parameter gives me the UserID of a valid user who has just logged in?

Ron

[quoted text, click to view]

Re: IF EXISTS?

Dan Guzman
9/24/2006 12:34:27 PM

You can return data using either output parameters or result set. Arnie's
example used a SELECT statement to return the value in a result set so one
method to retrieve that value is ExecuteScalar. Untested example:

Dim id As Int32 = Convert.ToInt32(sqlCmd.ExecuteScalar())

The following untested example below uses an output parameter:

CREATE PROCEDURE dbo.LoginUser
@UserName varchar(50),
@Password varchar(50),
@ID int OUTPUT
AS
SET NOCOUNT ON

SELECT @ID = UserID
FROM dbo.Users
WHERE UserName = @UserName
AND Password = @Password
GO

With sqlCmd
.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value =
txtUserName.Text
.Parameters.Add("@Password", SqlDbType.VarChar, 50).Value =
txtPassword.Text
.Parameters.Add("@ID", SqlDbType.Int, 4).Direction =
ParameterDirection.Output
End With

sqlConn.Open()
sqlCmd.ExecuteNonQuery()

If (sqlCmd.Parameters("@ID").Value = DBNull.Value) Then
Response.Write("Invalid User")
End If

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.