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

Question

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?

Take a look to: http://stackoverflow.com/questions/28893836/using-the-convert-in-where-clause-to-convert-varchar-to-date but in your case convert to varchar() — Rumpelstinsk, Nov 08 '16 at 13:39
@ClassId as an int will never possibly be able to hold all of the values that accessCode ***could*** be, you should probably work with a string as the parameter, and maybe tryparse it to an int... but this seems like you should just use two parameters. — Kritner, Nov 08 '16 at 13:40
you can do like this `OR accessCode = CAST (@ClassId AS VARCHAR(30))` — Jaydip Jadhav, Nov 08 '16 at 13:41
@juergend I can't change to a number since that's a string, it includes text — Laziale, Nov 08 '16 at 13:41
Are you on a weird SKU of SQL Server where you have to pay for each parameter you use? If not, I'd suggest using separate, named, parameters for each potential match rather than trying to create a swiss-army-knife parameter. — Damien_The_Unbeliever, Nov 08 '16 at 13:47

Chris J · Accepted Answer · 2016-11-08T15:01:57.470

1

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

edited Nov 08 '16 at 15:01

answered Nov 08 '16 at 14:01

Chris J

938
1
8
27

An `ISNUMERIC` check doesn't guarantee that the value can be converted to an `int`. It answers the question nobody ever wanted to ask "can this string be converted to any of the numeric types?". – Damien_The_Unbeliever Nov 08 '16 at 14:05
@Damien_The_Unbeliever thanks for the spot, updated the code to test specifically for integers as per this question http://stackoverflow.com/questions/2358147/best-equivalent-for-isinteger-in-sql-server – Chris J Nov 08 '16 at 14:10
I'd declare a variable above the `Select` statement and put that `Case` statement up there. No sense evaluating it for each record if you don't have to. – adam0101 Nov 08 '16 at 14:55

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

1 Answers1