Laziale Laziale - 24 days ago 9
SQL Question

SQL where clause with one parameter to check either int or string field

I have this stored procedure:

ALTER PROCEDURE [dbo].[getClassInfo](@ClassId int)
AS
BEGIN
SELECT [usergroupId]
,[gName]
,[accessCode]
FROM [dbo].[usergroup]
where usergroupid = @ClassId OR accessCode = @ClassId
END


Now, I want the parameter I'm sending to check either
usergroupId
which is a number or
accessCode
which is a string.

Which solution is painless to check for both cases using one parameter?

Answer

You can't have none integer values in your parameter so have changed to varchar.

Alter Procedure [dbo].[getClassInfo] ( @ClassId Varchar(50) )
As
    Begin
        If IsNumeric(@ClassId + '.0e0') = 1
            Begin
                Declare @ClassIdInt Int = Convert(Int , @ClassId);

                Select  [usergroupId]
                      , [gName]
                      , [accessCode]
                From    [dbo].[usergroup]
                Where   @ClassIdInt = [usergroupid];
            End; 
        If IsNumeric(@ClassId + '.0e0') = 0
            Begin
                Select  [usergroupId]
                      , [gName]
                      , [accessCode]
                From    [dbo].[usergroup]
                Where   [accessCode] = @ClassId;
            End;
    End;

The if statement determines if the value passed is an integer and then runs the appropriate query

Comments