Tukukkk

I have a table which has a column LanguageID. It has seven values: 1, 2, 3, 4, 5, 6, 22

Now I have a function that gets the languageID of the current user.

I want to check if that languageID exists in my table, if it exists return that value, if not then return default 2. If 2 doesn't exist in that table then return 1.

This is my query:

<cfquery name="NameName" datasource="mydatabase">

        SELECT DISTINCT SomeID,

        SomeName,

        LanguageID

        FROM myDatabase WITH(NOLOCK)

        WHERE BlacklistReasonID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.BlacklistReasonID#" />

        <cfif isDefined(arguments.LanguageID)> //I want to check if the arguments.LanguageID exists in the table

            AND LanguageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.LanguageID#" />

        <cfelseif NOT isDefined(arguments.LanguageID) > //ako nema value

            AND LanguageID = 2 //default, put languageID to 2

        <cfelse>

            AND LanguageID = 1 //if languageID 2 doesn't exist in that table, then put 1

        </cfif>

    </cfquery>

So real life example. The funtion activates, the LanguageID is 3. That's ok, the first cfif check to see if the table has a value of 3, it has, ok then it puts the languageID to 3 and ends.

Second scenario. The funtion activates, the LanguageID is 44. The first cfif checks to see if the table has a value of 44, it doesn't have that value, so the second cfif activates and puts the LanguageID to 2.

This can be done in the SQL itself. That would likely be much more performant than having ColdFusion try to do query processing.

I made a couple of assumptions, based on your original question. First, that this query should only return 1 result (TOP 1). And second, that your default languages are 2 for Default1 and 1 for Default2. This also includes that the value you are searching for will be greater than 1 or 2.

SELECT TOP 1 SomeID, SomeName, LanguageID

FROM (

  SELECT SomeID, SomeName, LanguageID

  FROM t1

  WHERE BlacklistReasonID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.BlacklistReasonID#">

    AND LanguageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Language#">



  UNION ALL 



  SELECT SomeID, SomeName, LanguageID

  FROM t1 

  WHERE BlacklistReasonID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.BlacklistReasonID#">

    AND LanguageID IN ( <cfqueryparam value="1,2" cfsqltype="CF_SQL_INTEGER" list="yes"> ) /* DEFAULT IDs */

) s1

ORDER BY id DESC

;

This works because a query will return no results if the filtering condition is not matched, but when joined to a query that does have results (your defaults) those will appear in the whole query. Then I order the results and take the top 1.

The basic gist of the query is at: https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=6f729939d3305e49032eab56d88aa877

I also included several demonstrations of how this works if your LanguageID is not found, and one possible way of using default IDs that aren't 1 or 2.

And to keep it all in one place:

CREATE TABLE t1 ( id int, lang varchar(10) ) ;

INSERT INTO t1 (id, lang)

VALUES (1,'Default 2'),(2,'Default 1'),(3,'Klingon')

  ,(50, 'Common'),(20, 'Nadsat'),(55, 'Furbish')

;

/* If you will only have one language per id, and default IDs will always be 2 and 1 */

/* Match */

SELECT TOP 1 id, lang

FROM (

  SELECT id, lang 

  FROM t1

  WHERE id = 3



  UNION ALL 



  SELECT id, lang

  FROM t1 

  WHERE id IN (1,2) /* DEFAULT IDs */

) s1

ORDER BY id DESC

;

id | lang   

-: | :------

 3 | Klingon

/* No match. */

SELECT TOP 1 id, lang

FROM (

  SELECT id, lang 

  FROM t1

  WHERE id = 99999



  UNION ALL 



  SELECT id, lang

  FROM t1 

  WHERE id IN (1,2) /* DEFAULT IDs */

) s1

ORDER BY id DESC

;

id | lang     

-: | :--------

 2 | Default 1

/* No Default1 Language */

CREATE TABLE t2 ( id int, lang varchar(10) ) ;

INSERT INTO t2 (id, lang)

VALUES (1,'Default 2'),(3,'Klingon')

  ,(50, 'Common'),(20, 'Nadsat'),(55, 'Furbish')

;



SELECT TOP 1 id, lang

FROM (

  SELECT id, lang 

  FROM t2

  WHERE id = 9999



  UNION ALL 



  SELECT id, lang

  FROM t2 

  WHERE id IN (1,2) /* DEFAULT IDs */

) s1

ORDER BY id DESC

;

id | lang     

-: | :--------

 1 | Default 2

/* No Default Language */

CREATE TABLE t3 ( id int, lang varchar(10) ) ;

INSERT INTO t3 (id, lang)

VALUES (3,'Klingon')

  ,(50, 'Common'),(20, 'Nadsat'),(55, 'Furbish')

;



/* No match. No Default. */

SELECT TOP 1 id, lang

FROM (

  SELECT id, lang 

  FROM t3

  WHERE id = 9999



  UNION ALL 



  SELECT id, lang

  FROM t3 

  WHERE id IN (1,2) /* DEFAULT IDs */

) s1

ORDER BY id DESC

;

id | lang

-: | :---

/* Match, but no default */

SELECT TOP 1 id, lang

FROM (

  SELECT id, lang 

  FROM t3

  WHERE id = 3



  UNION ALL 



  SELECT id, lang

  FROM t3 

  WHERE id IN (1,2) /* DEFAULT IDs */

) s1

ORDER BY id DESC

;

id | lang   

-: | :------

 3 | Klingon

/* Default 1 and 2 are not ID 2 and 1. */

CREATE TABLE t4 ( id int, lang varchar(10) ) ;

INSERT INTO t4 (id, lang)

VALUES (40,'Default 2'),(42,'Default 1'),(3,'Klingon')

  ,(50, 'Common'),(20, 'Nadsat'),(55, 'Furbish')

;



/* No match. Pick Default. */

SELECT TOP 1 s1.id, s1.lang

FROM (

  SELECT id, lang 

  FROM t4

  WHERE id = 9999



  UNION ALL 



  SELECT s2.id, s2.lang 

  FROM (

    SELECT TOP 1 id, lang

      , CASE 

          WHEN ID = 42 THEN 2 

          WHEN ID = 40 THEN 1 

          ELSE 0 

        END AS sortOrder

    FROM t4 

    WHERE id IN (40,42) /* NEW DEFAULT IDs */

    ORDER BY sortOrder DESC

  ) s2

) s1

ORDER BY s1.id DESC

;

id | lang     

-: | :--------

42 | Default 1

db<>fiddle here