IF EXISTS IN STORED PROCEDURE

Question

hi guys,

I am using following storedprocedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO


Alter PROCEDURE [dbo].[GetUserDetailsByUserID]
(
    @UserId varchar(100)
)
AS
BEGIN

    IF EXISTS(Select * from Registration where UserId=@UserID)
     BEGIN
      Select
          [Name],
          [UserId],
          [PermanentAddress],
          [TemporaryAddress],
          [OfficePhoneNo],
          [ResidentialPhoneNo],
          [MobileNo],
          [Email],
          [ContactPerson],
          [C_OfficePhoneNo],
          [C_ResidentialPhoneNo],
          [C_MobileNo],
          [C_Email],
          [Project],
          [TotalAmount]
      From
       Registration
      Where
       UserId=@UserId

     END

END

I am using following code in vb

Public Function GetUserDetailsByUserID(ByVal strUserId As String) As DataTable
        Try

            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim DbCommand As DbCommand = _
                db.GetStoredProcCommand("GetUserDetailsByUserID")
            db.AddInParameter(DbCommand, "@UserId", DbType.String, strUserId)
            Return db.ExecuteDataSet(DbCommand).Tables(0)

        Catch ex As Exception
            Return New DataTable()
        End Try
    End Function

If details corresponding to userid does not exist in registration table, db.ExecuteDataSet(DbCommand).Tables(0) shows one error as cannot find Table(0). What modification in stoted procedure i hve to make to get rid of this error?

Answer 1

The procedure will not always return a record set. If there is no record set then Tables will be empty and Tables(0) will fail and return an error. You should just return the selection rather than only selecting if the record exists. Your code can then check for an empty returned record set.

Answer 2

You can simply get rid of the IF EXISTS. When the record doesn't exist, you will get an empty table (which I think is what you want, looking at your sample code)

Answer 3

In the VB code, change

Return db.ExecuteDataSet(DbCommand).Tables(0)

to

Dim Ds as DataSet = db.ExecuteDataSet(DbCommand)
If Ds.Tables.Count = 1 Then
    Return Ds.Tables(0)
Else
    Return New DataTable()
End If

Also, remove the If Exists from the Stored Procedure, since if the row exists, you will force the database to search twice in the table for the record where UserId=@UserID.