CODEWITHSUNDEEP
sqlsql-servert-sql
user10130439
user10130439

Reputation:

Apply IF conditional at the end of the query

I'm new in sql server and I have WHERE clause like this:

WHERE[D].[IsLocked] = 0
AND(@StartDate IS NULL OR ISNULL([TA].[ModifiedDate], [TA].[CreationDate]) >= @StartDate)
AND(@EndDate IS NULL OR ISNULL([TA].[ModifiedDate], [TA].[CreationDate]) <= @EndDate)
AND((CASE WHEN[T].[TaskStatusId] = '09E02513-00AD-49E3-B442-A9ED2833FB25'
  THEN 1 ELSE 0 END) = @Completed)
AND((@FilterEmpKey IS NULL AND[TA].[EmpKey] = @CurrentEmpKey) 
OR (ISNULL([TA].[ModifiedAssignedBy], [TA].[AssignatedBy]) = @FilterEmpKey 
AND[TA].[EmpKey] = @CurrentEmpKey))

But now I want to add if conditional in order to add more filters at the end of query like:

  IF(@FilterEmpGuid IS NOT NULL)
    AND[TA].[EmpKey] = @CurrentEmpKey
    AND[TA].[AssignatedBy] = @CurrentEmpKey
    AND[TA].[EmpKey] = @FilterEmpKey

But I get:

The multi-part identifier [TA].[EmpKey] could not be bound

What am I doing wrong?

Upvotes: 1

Views: 63

Answers (2)

critical_error
critical_error

Reputation: 6706

You could move the additional filter options into a scalar function.

If you know the additional fields that may be filtered, you may be able to get away with something like:

CREATE FUNCTION dbo.ExtendFilter(
    @column_value VARCHAR(50), @param_value VARCHAR(50)
)
RETURNS BIT
AS
BEGIN

    DECLARE @return BIT = 1;    -- default RETURN to 1 ( include ).

    IF ( NULLIF( @param_value, '' ) IS NOT NULL )
    BEGIN

        -- compare the column's value to the param value
        IF ( @column_value <> @param_value )
            SET @return = 0;    -- don't include this record.

    END

    RETURN @return;

END
GO

And then use it like:

WHERE
    { other WHERE stuff }
    AND dbo.ExtendFilter( [TA].[EmpKey], @CurrentEmpKey ) = 1
    AND dbo.ExtendFilter( [TA].[AssignatedBy], @CurrentEmpKey ) = 1
    AND dbo.ExtendFilter( [TA].[EmpKey], @FilterEmpKey ) = 1

Mind you this is just an example. You'd want to check @pram_value for NULL, etc...

Upvotes: 0

monty
monty

Reputation: 1590

IF conditionals are only for use outside sql queries, such as in procedures etc.

In a query itself you are limited to AND, OR and CASE statements, so you will need to rewrite your IF conditional for this:

AND (@FilterEmpGuid IS NULL 
     OR (
        [TA].[EmpKey] = @CurrentEmpKey
        AND[TA].[AssignatedBy] = @CurrentEmpKey
        AND[TA].[EmpKey] = @FilterEmpKey
     ))

Upvotes: 2

Related Questions