SQL Server Conditional Flow

SQL Server : executing query conditionally

The issue is the compile stage versus the execution stage. The code is initially compiled, where the tables and columns are validated.

Your problem is that the column doesn't exist, so you are getting a compile failure.

You can fix this using dynamic SQL:

DECLARE @sql NVARCHAR(max);
IF (COL_LENGTH('Employees', 'EmpID') IS NOT NULL)
    SET @sql = N'
Select EmpID, NAME, SEX, SALARY
From Employees
Where EmpID IS NOT NULL'
ELSE
    SET @sql = N'
Select ID, NAME, SEX, SALARY
From Employees
Where ID IS NOT NULL'

EXEC sp_executesql @sql;

Conditional Where clause in SQL query if one or both date range parameters are null

Don't cast your column, it'll be bad for performance. I also assume that your parameters are date data types, but that your column is actually a date and time (such as a datetime2), due to your casting. You can then just use a single ISNULL on the parameters, and then an OR for when they are both NULL:

WHERE (CreateDate >= @StartDate AND CreateDate < DATEADD(DAY,1,ISNULL(@EndDate,@StartDate))
   OR (@StartDate IS NULL AND @EndDate IS NULL)

You'll probably want to put an OPTION (RECOMPILE) in there too, as the plan for when they both have NULL could be very different to when they don't.

Conditional group by and select query sql

Tr like below

     select sum(totalamount),
    (case when  Type ='1' then typetitle else Type end)
    from #temp
     group by (case when  Type =1 then typetitle else Type end)

In group by projection column name and group by column have to be same

Conditional CROSS APPLY in SQL Server

You can use a CROSS APPLY with a virtual table to do some calculations, then pass those to the function.

I don't know the meaning of the calculations, so I've just give them generic names. I also don't know the correctness of your logic, I've just copied what you've shown.

SELECT 
    t.ScheduleId,
    t.BaseDate,
    t.AfterDate,
    t.memberagreementitemid,  
    fn.ScheduleDate AS NextBillingDate
INTO 
    #Distinct_BillableMemberAgreementItems
FROM 
    Distinct_BillableMemberAgreementItems_CTE t

CROSS APPLY (
    SELECT
        CASE WHEN ISNULL(t.PreviousBillingDate, t.LastInvoicedDate) = GETDATE()
               OR (t.BaseDate <> GETDATE() AND t.FromBilling = 0)
          THEN ISNULL(t.PreviousBillingDate, t.BaseDate)
          ELSE t.AfterDate
        END,
        CASE WHEN ISNULL(t.PreviousBillingDate, t.LastInvoicedDate) = GETDATE()
               OR (t.BaseDate <> GETDATE() AND t.FromBilling = 0)
          THEN 2
          ELSE 1
        END
) v1(SomeCalculation1, SomeCalculation2)

CROSS APPLY dbo.fn_ScheduleCalculator(
    t.ScheduleId,
    t.BaseDate,
    v1.SomeCalculation1,
    0,
    v2.SomeCalculation2,
    NULL, NULL, NULL
  ) fn
WHERE
    (v1.SomeCalculation2 = 1 OR fn.RowNumber = 2);

Execute Data Flow Task based on condition in SSIS

The dummy script approach:

In the expressions for the two precedence constraints you evaluate a variable that is set either by the first SQL task or the script task.

Now, the data will flow down one of the two data flow paths based on the value of the variable.
The script task can be empty, or it can perform the logic needed to set the variable.

IF condition expression evaluation in TSQL

You can't rely on SQL Server not evaluating the second expression if the first one is false. See my answer to the question linked by Martin in his comment.

T-SQL Conditional Order By

CASE is an expression that returns a value. It is not for control-of-flow, like IF. And you can't use IF within a query.

Unfortunately, there are some limitations with CASE expressions that make it cumbersome to do what you want. For example, all of the branches in a CASE expression must return the same type, or be implicitly convertible to the same type. I wouldn't try that with strings and dates. You also can't use CASE to specify sort direction.

SELECT column_list_please
FROM dbo.Product -- dbo prefix please
ORDER BY 
  CASE WHEN @sortDir = 'asc' AND @sortOrder = 'name' THEN name END,
  CASE WHEN @sortDir = 'asc' AND @sortOrder = 'created_date' THEN created_date END,
  CASE WHEN @sortDir = 'desc' AND @sortOrder = 'name' THEN name END DESC,
  CASE WHEN @sortDir = 'desc' AND @sortOrder = 'created_date' THEN created_date END DESC;

An arguably easier solution (especially if this gets more complex) is to use dynamic SQL. To thwart SQL injection you can test the values:

IF @sortDir NOT IN ('asc', 'desc')
  OR @sortOrder NOT IN ('name', 'created_date')
BEGIN
  RAISERROR('Invalid params', 11, 1);
  RETURN;
END

DECLARE @sql NVARCHAR(MAX) = N'SELECT column_list_please
  FROM dbo.Product ORDER BY ' + @sortOrder + ' ' + @sortDir;

EXEC sp_executesql @sql;

Another plus for dynamic SQL, in spite of all the fear-mongering that is spread about it: you can get the best plan for each sort variation, instead of one single plan that will optimize to whatever sort variation you happened to use first. It also performed best universally in a recent performance comparison I ran:

http://sqlperformance.com/conditional-order-by

SQL Server - Conditional LEFT JOIN with conditions on both sides

I believe that you just need to cast the int into varchar

SELECT    per.Name, post.Category 
FROM      Person per
          LEFT JOIN WorkAddress wa ON per.WorkAddressID = wa.ID
          LEFT JOIN HomeAddress ha ON per.ID = ha.PersonID
          LEFT JOIN Postcode post ON 
          (CASE WHEN per.WorkAddressID IS NOT NULL THEN CAST(wa.PostCodeID AS varchar(100)) ELSE ha.PostCode+ha.Suburb END) =
          (CASE WHEN per.WorkAddressID IS NOT NULL THEN CAST(post.ID AS varchar(100)) ELSE post.PostcodeSuburb END)

sqlfiddle

The problem, in this case, seems to be caused by fact that SQL Server always does the implicit conversion from varchar to int (if int and varchar are compared). You can try it with the following examples:

-- #1 example
with data as
(
 select 10 a, '10' b
)
select * from data where a = b;

-- #2 example
with data as
(
 select 10 a, 'b' b
)
select * from data where a = b;

dbfiddle

EDIT: as mentioned by @dnoeth, CASE requires that all return paths return the same data type and therefore SQL Server does the varchar conversion due to the int precedence (as shown on the above example).

---

Related Topics