Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Charlotte, NC
    Posts
    1

    Unanswered: Limitations of the WHERE IN clause

    I am currently writing a report that allows users to select up to fifty six different health plans to view. The report OCX passes a comma delimited string to the stored procedure with all of the health plan ids.The problem is that the stored procedure that I wrote uses a "WHERE IN" clause that seems to have a size limitation. The report works fine if I select less than that 39 health plans. It fails with an error if I select more than that.

    Here is the stored procedure code as well as the error I receive:

    Create PROCEDURE proc_targetpop_rep2
    @mystring varchar(4000),
    @begindate datetime,
    @enddate datetime
    AS


    DECLARE @instructsql varchar(1000)
    SET @instructsql = 'SELECT DISTINCT tblhealth_plan.carrier_entity_id, tblcoverage.cons_entity_id,
    tblhealth_plan.health_plan_sysid, tblhealth_plan.health_plan_desc, tblperson.last_name,
    tblperson.first_name, tblhealth_plan.effect_date, tblhealth_plan.term_date,
    ''' + CONVERT(CHAR(10), @begindate, 120) + ''' as repbegindate, ''' + CONVERT(CHAR(10), @enddate, 120) + ''' as rependdate
    FROM (tblcoverage
    INNER JOIN tblhealth_plan ON tblcoverage.health_plan_sysid = tblhealth_plan.health_plan_sysid)
    INNER JOIN tblperson ON ((tblcoverage.cons_entity_type_code = tblperson.pers_entity_type_code)
    AND (tblcoverage.cons_entity_id = tblperson.pers_entity_id))
    WHERE tblhealth_plan.carrier_entity_id = 197
    AND tblhealth_plan.effect_date >= ''' + CONVERT(CHAR(10), @begindate, 120) + '''
    AND tblhealth_plan.effect_date <= ''' + CONVERT(CHAR(10), @enddate, 120) + '''
    AND tblhealth_plan.health_plan_sysid IN
    (Select tblhealth_plan.health_plan_sysid
    From tblhealth_plan
    Where tblhealth_plan.health_plan_sysid in ('+@mystring+'))'


    print @instructsql
    execute (@instructsql)

    GO

    This is what happens when I run the SPROC with all possible selections:

    SELECT DISTINCT tblhealth_plan.carrier_entity_id, tblcoverage.cons_entity_id,
    tblhealth_plan.health_plan_sysid, tblhealth_plan.health_plan_desc, tblperson.last_name,
    tblperson.first_name, tblhealth_plan.effect_date, tblhealth_plan.term_date,
    '2002-09-01' as repbegindate, '2003-10-01' as rependdate
    FROM (tblcoverage
    INNER JOIN tblhealth_plan ON tblcoverage.health_plan_sysid = tblhealth_plan.health_plan_sysid)
    INNER JOIN tblperson ON ((tblcoverage.cons_entity_type_code = tblperson.pers_entity_type_code)
    AND (tblcoverage.cons_entity_id = tblperson.pers_entity_id))
    WHERE tblhealth_plan.carrier_entity_id = 197
    AND tblhealth_plan.effect_date >= '2002-09-01'
    AND tblhealth_plan.effect_date <= '2003-10-01'
    AND tblhealth_plan.health_plan_sysid IN
    (Select tblhealth_plan.health_plan_sysid
    From tblhealth_plan
    Where tblhealth_plan.health_plan_sysid in (367,368,369,370,371,372,373,374,375,376,377,378,3 79,380,381,382,383,384,385,386,387,388,389,390,391 ,392,393,394,395,396,3
    Server: Msg 170, Level 15, State 1, Line 15
    Line 15: Incorrect syntax near '3'.

    It is really strange in that it works fine if I select less than 39 health plans. The only thing I can think of is that the "IN" clause will only accept up to so many characters.

    Just wondering if anyone has any ideas. Thanks in advance!!

    Cindy

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, after just 39 selections your query is more than 980 characters long, so I don't think you are going to fit 56 selections into a varchar(1000) variable.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •