1
select *
from temp tp
join a 
on    tp.id = a.id
where (  a.enddate   between @startdate and @endDate
      or a.startdate between @startdate and @endDate)
and   a.id1 in (case when (isnull(@id,''<>'')
                     then (select distinct id from #temp2)
                     else a.id1
                end)

The #temp2 query return multiple rows.Is that possible to achieve through case stament or else please do suugest the best possible way.

UDPATE: I am using the case statement as when the @id is null it should return all the records (a.id1=1,2,3,4,5,6)but when the @id=1,2,3 is not null it should return only those records which have the a.id1=1,2,3.

2
  • Can you include a tag specifying the database software you are using? Commented Jul 20, 2017 at 15:28
  • my currently using sql server 2014
    – mounica
    Commented Jul 20, 2017 at 18:32

2 Answers 2

1

NOTE #1: Without knowing which RDMBS you're using I can't say if your proposed query will work or not (I'm leaning towards 'doubtful').

NOTE #2: I'm assuming the ( just before the isnull is not supposed to be there, otherwise I'm not sure what logic you're trying to implement, ie, you'll need to update your query with matching parens.

I'm thinking you'll want to pull the isnull test out to the top level and use an or to satisfy your conditional test, eg:

-- using '@id in (select ...)' construct

select *
from   temp tp
join   a 
on     tp.id = a.id

where  (  a.enddate   between @startdate and @endDate
       or a.startdate between @startdate and @endDate)

and    (   
            @id is not NULL
        or
           (@id is NULL and a.id1 in (select distinct id from #temp2) )
       )

-- or
-- using 'exists (select ...)' construct

select *
from   temp tp
join   a 
on     tp.id = a.id

where  (  a.enddate   between @startdate and @endDate
       or a.startdate between @startdate and @endDate)

and    (   
            @id is not NULL
        or
           (@id is NULL and exists(select 1 from #temp2 t where t.id=a.id1) )
       )
4
  • thank you for your response.But what I am trying to achieve is my #temp2 returns multiple values and I want to send the multiple value to filter out the data like[where id in(case when(condition)then(select 8 from #temp) else id end]
    – mounica
    Commented Jul 20, 2017 at 19:48
  • @mounica: whether you use id in (select id from #temp2) or exists(select 1 from #temp2 t where t.id=a.id1), they accomplish the same thing; I've updated my answer to show both versions
    – markp-fuso
    Commented Jul 20, 2017 at 19:56
  • this works but this is not what I am trying to do..I am using the case statement as when the @id is null it should return all the records (a.id1=1,2,3,4,5,6)but when the @id=1,2,3 is not null it should return only those records which have the a.id1=1,2,3.Hope this makes my question clear.thank you againg.
    – mounica
    Commented Jul 20, 2017 at 20:17
  • that's not what your original post/query is saying; your (original, pseudo) query is saying a) if @id is null then check for a.id1 in (select id from #temp2) or b) if @id is not NULL then check for a.id1=a.id1; if my understanding is incorrect then I suggest you give some thought to editing/updating your original post/query to better explain what it is you're trying to accomplish; it will probably help if you take a look at How to create a Minimal, Complete and Verifiable example
    – markp-fuso
    Commented Jul 20, 2017 at 20:26
0

If I understand correctly, you have a variable @id which will either contain a comma-sparated list of ID values, the string <>, or be NULL. If it has ID values, the rows you're searching for must have one of the IDs specified; Otherwise, the rows you're searching for must match one of the IDs from the table #temp2.

Unfortunately, you cannot use a string constant as a list of values for an IN comparison directly. There are basically two ways to proceed from this point. You can build your statement as a dynamic SQL statement (concatenating in the list of IDs), or parse the individual IDs out of the string, load them into a table, and use that.

We'll go for the dynamic SQL, as I think it'll be easier to follow:

DECLARE @stmt NVARCHAR(max);

SET @stmt = N'SELECT *
  FROM temp tp
         INNER JOIN a ON (tp.id = a.id)
 WHERE (   a.enddate BETWEEN @startdate AND @enddate
        or a.startdate BETWEEN @startdate AND @enddate
       )
and   a.id1 in ('
+ CASE WHEN ISNULL(@id,'<>') = '<>'
    THEN N'SELECT DISTINCT id FROM #temp2'
    ELSE @id
  END
+ ');'
;

EXECUTE sp_executesql @stmt
                     ,N'@startdate datetime, @enddate datetime'
                     ,@startdate = @startdate
                     ,@enddate = @enddate;

When using the sp_executesql stored procedure, you need to give it a statement to execute; you can also give it a list of parameters, followed by assignments of the values of those parameters. Here, I've used the same name for the parameters, and the variables we'll use to populate them. For the statement and the list of parameters, sp_executesql requires nvarchar type strings; that's why the string constants start with N'.

The statement we build is hard-coded (with parameters for the start and end dates), except for what's actually in the IN list. Your CASE statement goes there, putting in the list of explicit IDs if you have one, and the SELECT from #temp2 if you don't.

UPDATE: If you really have a problem with the dynamic SQL solution, then you can use a stored procedure, a function, or a CLR to split your comma-separated list of integer values into a temp table. There are many ways to do this: here's a link to an article discussing some of the options, with a recommended solution. There are many others - type "SQL Server" split string and you'll get about 507,000 results. Ignore any that talk about the STRING_SPLIT function available starting in SQL Server 2016, and that's not your version, of course. Given that your strings will presumably not be incredibly long, any of the pre-2016 solutions might work well enough for you. If your list of IDs might grow to more than 1,000 items, you might want to consider looking closely at Aaron's post (the link above) and seeing which options still perform reasonably.

If you use the SplitInts_IOnline_2 function from Aaron's blog post:

CREATE FUNCTION dbo.SplitInts_Inline_2
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN
   (
       SELECT Item = CONVERT(INT, (SUBSTRING(
           @Delimiter + @List + @Delimiter, 
           w.n + 1,
           CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.n + 1) - w.n - 1
           )))
       FROM
       (

           SELECT n = v0.n + v1.n + v2.n + v3.n
               FROM  
               (
                   SELECT n = 0 
                   UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL SELECT 3
                   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL SELECT 6
                   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL SELECT 9
                   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
                   UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
               ) AS v0,
               (
                   SELECT n = 0 
                   UNION ALL SELECT 16  UNION ALL SELECT 32  UNION ALL SELECT 48
                   UNION ALL SELECT 64  UNION ALL SELECT 80  UNION ALL SELECT 96
                   UNION ALL SELECT 112 UNION ALL SELECT 128 UNION ALL SELECT 144
                   UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL SELECT 192
                   UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
               ) AS v1,
               (
                   SELECT n = 0
                   UNION ALL SELECT 256  UNION ALL SELECT 512  UNION ALL SELECT 768 
                   UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 
                   UNION ALL SELECT 1792 UNION ALL SELECT 2048 UNION ALL SELECT 2304
                   UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL SELECT 3072
                   UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
               ) AS v2,
               (
                   SELECT n = 0
                   UNION ALL SELECT 4096   UNION ALL SELECT 8192   UNION ALL SELECT 12288
                   UNION ALL SELECT 16384  UNION ALL SELECT 20480  UNION ALL SELECT 24576
                   UNION ALL SELECT 28672  UNION ALL SELECT 32768  UNION ALL SELECT 36864
                   UNION ALL SELECT 40960  UNION ALL SELECT 45056  UNION ALL SELECT 49152
                   UNION ALL SELECT 53248  UNION ALL SELECT 57344  UNION ALL SELECT 61440
                   UNION ALL SELECT 65536  UNION ALL SELECT 69632  UNION ALL SELECT 73728
                   UNION ALL SELECT 77824  UNION ALL SELECT 81920  UNION ALL SELECT 86016
                   UNION ALL SELECT 90112  UNION ALL SELECT 94208  UNION ALL SELECT 98304
                   UNION ALL SELECT 102400 UNION ALL SELECT 106496 UNION ALL SELECT 110592
                   UNION ALL SELECT 114688 UNION ALL SELECT 118784 UNION ALL SELECT 122880 
                   UNION ALL SELECT 126976 UNION ALL SELECT 131072 UNION ALL SELECT 135168
                   UNION ALL SELECT 139264 UNION ALL SELECT 143360 UNION ALL SELECT 147456
               ) v3
       ) w
       WHERE w.n = CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.n)
       AND w.n < LEN(@Delimiter + @List)
   );

Then your code would look like this:

IF ISNULL(@id,'<>' = '<>')
BEGIN
    SELECT tp.*, a.*
      FROM temp tp
             INNER JOIN a ON (tp.id = a.id)
     WHERE (   a.enddate BETWEEN @startdate AND @enddate
            or a.startdate BETWEEN @startdate AND @enddate
           )
       AND EXISTS (SELECT 1 FROM #temp2 WHERE is = a.id1)
END
ELSE
BEGIN
    SELECT tp.*, a.*
      FROM temp tp
             INNER JOIN a ON (tp.id = a.id)
               INNER JOIN dbo.SplitInts_Inline_2(@id,',') ids ON (a.id1 = ids.Item)

     WHERE (   a.enddate BETWEEN @startdate AND @enddate
            or a.startdate BETWEEN @startdate AND @enddate
           )
END;

Note that this assumes your list will have no duplicate values in it. I don't see any deduplication in the function, so to eliminate duplicates, you'd have to replace the iiner join to the function above with:

INNER JOIN (SELECT DISTINCT Item FROM dbo.SplitInts_Inline_2(@id,',')) ids ON (a.id1 = ids.Item)

Also note that the EXISTS check on #temp2 can also be an INNER JOIN to a if there are no duplicates in that table. I made that an EXISTS to ensure that we only get one copy of the rows in a, even if there is more than one matching row in #temp2.

Like I said, I think the dynamic SQL option is easier to follow.

1
  • thank you this was my question but is there any other way other than using the sp_executesql..to achieve this issue..
    – mounica
    Commented Jul 24, 2017 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.