logo
down
shadow

CASE CLAUSE IN WHERE CONDITION


CASE CLAUSE IN WHERE CONDITION

By : user2950280
Date : November 17 2020, 11:52 AM
To fix this issue I want to create a stored procedure that accepts an optional argument. I want to accomplish something like the following: , Try this.
code :
CREATE PROC myProc(@myCriteria Int) AS
SELECT myFieldList
FROM myTable
WHERE (myCriteria = @myCriteria) or (@myCriteria IS NULL and 1=1) 
CREATE PROC myProc(@myCriteria Int) AS
SELECT myFieldList
FROM myTable
WHERE (@myCriteria is null OR myCriteria = @myCriteria) 


Share : facebook icon twitter icon
SQL IF ELSE / CASE clause in WHERE condition

SQL IF ELSE / CASE clause in WHERE condition


By : ivanrasinul
Date : March 29 2020, 07:55 AM
This might help you The most elegant and efficient solution with a sophisticated query optimizer would be this:
code :
SELECT * FROM (
    SELECT * FROM `table`
    WHERE ID IN (
      SELECT id FROM `table` 
      WHERE lang = 'EN'
      EXCEPT
      SELECT id FROM `table`
      WHERE lang = 'FR'
    ) OR table.LANG ='FR'
) t1
WHERE id = ?
  SELECT * FROM `table`
    WHERE id = ? AND (ID IN (
      SELECT id FROM `table` 
      WHERE lang = 'EN' AND ID = ?
      EXCEPT
      SELECT id FROM `table`
      WHERE lang = 'FR' AND ID = ?
    ) OR table.LANG ='FR')
SELECT * FROM `table`
WHERE ID IN (
  SELECT id FROM `table` 
  WHERE lang = 'EN'
  EXCEPT
  SELECT id FROM `table`
  WHERE lang = 'FR'
) OR table.LANG ='FR'
SELECT * FROM `table`
WHERE lang = 'FR'
OR (lang = 'EN' AND ID NOT IN (SELECT ID FROM table WHERE lang = 'FR'))
SELECT * FROM `table`
WHERE lang = 'FR'
OR (table.LANG = 'EN' AND NOT EXISTS (SELECT * FROM table t1 WHERE lang = 'FR' AND t1.id = table.id))
T-SQL Case Condition in Where Clause

T-SQL Case Condition in Where Clause


By : user3438374
Date : March 29 2020, 07:55 AM
Hope that helps i trying to do this query where i have a where clause. The problem is that i need to use inside the where condition the operator IN but i can´t figured out what i missing. , There's no need for a case statement.
code :
DECLARE @OP INT = 1;
SELECT * FROM Table
WHERE (@OP = 1 AND Table.[status] IN (5,6))
OR (@OP !=1 AND Table.[status] IN (12))
Case When Condition in Where Clause. Use filter condition if it matches the case when condition only

Case When Condition in Where Clause. Use filter condition if it matches the case when condition only


By : Kumar Atul
Date : March 29 2020, 07:55 AM
Hope that helps Is it possible to use case when condition in where clause to filter select statement. For Eg: , You don't need case at all:
code :
Select * from table_name 
where source ='UHC'
and ((policy_number = '1' and to_char(termdate,'YYYYMM') <= '201603') 
     or nvl(policy_number, '0') != '1');
where source ='UHC' and case when policy_number = '1' then to_char(termdate,'YYYYMM') else '000000' end <= '201603');
Add diferent condition into where clause depending on case condition

Add diferent condition into where clause depending on case condition


By : Dibya Nandan Mishra
Date : March 29 2020, 07:55 AM
Does that help If column A is not empty I should add one condition and if it is empty, then I should add another condition. Something like this: , We can rephrase the login in the WHERE clause to make it work:
code :
SELECT *
FROM table_t
WHERE
    (LEN(t.A) > 0   AND t.A IN (SELECT B FROM anothertable) ) OR
    (LEN(t.A) <= 0) AND t.C IN (SELECT D FROM anothertable) );
SQL Where Clause with CASE & NOT IN Condition

SQL Where Clause with CASE & NOT IN Condition


By : Rezan Upadhyay
Date : March 29 2020, 07:55 AM
it should still fix some issue I have a select query with where Clause. Now I have to add additional condition in where clause based on user access. If user does not have access then need to include additional condition in where clause, else if user have access then there is no additional logic. , You can use CASE in WHERE as Shown below:
code :
    WHERE 1=(
             CASE WHEN @X = 0 and @y = 0 and pagecode not in ('admin','external') THEN 1
                  WHEN @x = 0 and @y = 1 and pagecode not in ('admin') THEN 1
                  WHEN @x = 1 and @y = 0 and pagecode not in ('external') THEN 1
                  ELSE 0 END
            )
    WHERE 1=(
             CASE WHEN @X = 0 and @y = 0 and pagecode not in ('admin','external') THEN 1
                  WHEN @x = 0 and @y = 1 and pagecode not in ('admin') THEN 1
                  WHEN @x = 1 and @y = 0 and pagecode not in ('external') THEN 1
                  WHEN @x = 1 and @y = 1 THEN 1
                  ELSE 0 END
            )
Related Posts Related Posts :
  • FreeTDS working, but ODBC cannot connect
  • How to change datetime format of varchar datatype column
  • Trigger on extended properties? SQL SERVER
  • Database Schema guidance for an app store
  • sql trigger to update based on specific column is updated and if value is equal to 'x'
  • How to descope a local variable in t-sql
  • SQL Server Compact stored procedure with multiple values in 1 input
  • How to validate the ICCID number of a SIM card in TSQL?
  • how to calculate all previous datas sum of a sql column on a date range report
  • How to create view listing all friendship for "friend1,friend2" table in T-SQL and prevent inserting duplicate
  • Sum of multiple column in SQL Server
  • Check month and day lies between two dates
  • How would I configure Effort Testing Tool to mock Entity Framework's DbContext withOut the actual SQL Server Database up
  • While looping through a function, Error: Maximum stored procedure, function, trigger, or view nesting level exceeded (li
  • Collapsing records with adjacent start and end dates
  • SQL Server Management Studio 2008 - Timeout Expired error
  • Insert date from VB to SQL Server
  • How to find what table foreign key is from?
  • Sql Server Nested Inserts possible
  • Deterministic way of getting WeekEnding (Sun) for given date column (for use in computed column)
  • SQL Server, why does one order by query work with a string, but not with an integer?
  • How do I install a SQL Server on the hosted build agent in Visual Studio Online?
  • EXECUTE AS/REVERT within a transaction in SQL Server
  • Distinct 2 field AS one column SQL SERVER
  • how to create a new database after a trigger fired from a table
  • How to sort string alphabetically
  • Checking data in one column in a temp table to a column in another table
  • What's wrong with my IF/ELSE? "ELSE: Incorrect syntax near 'ELSE'."
  • TSQL - Creating a running total of a column
  • Why am I getting not null here?
  • Why does sp_refreshview raise an exception on views that are not schema bound?
  • EXEC parameters from a SELECT
  • SQL Server Compact connection
  • Star Schema Structure - To many Dimensions
  • sort utf string in sql server with join clause
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org