Sign in
 
Home20122008 R2200820052000LibraryForums
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
SQL Server >
> 
> 
Problem on Variable #of arguments to UDF/stored procedure

Respondida Problem on Variable #of arguments to UDF/stored procedure

  • Monday, January 21, 2013 7:40 PM
     
      Has Code
    Sign In to Vote
    0
    I have a problem I'd like to solve in SQL, but not sure if it's possible..so some ideas would be appreciated.
    Problem is related to the healthcare domain. Patients can get classified using various codes, for example, let's say the hypertension diagnosis codes are ht1, ht2, ht3 and the diabetes codes are dm1, dm2, dm3, dm4. Patients normally get classified with a single code, so I'd like to pass the list of codes (may be as a comma separated list) to a UDF/stored procedure for further processing. First I need to identify patients who have multiple conditions - say the query is "Patients with hypertension and diabetes". Someone may think you can easily store all the hypertension codes in a table and the diabetes codes in another table and do a simple join on the 3 tables, but the problem is the codes that constitute the conditions can change which is why I want to pass the lists of conditions to the UDF. I'm looking for a fairly generic solution instead of a brute force type mechanism if there is one, cos you may want to check for patients with 3 or 4 or 'n' conditions as well. In terms of the data, I have is a PATIENTS table: 
    CREATE TABLE #PATIENTS (ID varchar(10), Code varchar(20))
INSERT INTO #PATIENTS    (ID, Code)
SELECT 1    , 'p1', 'ht2' UNION ALL
SELECT 2    , 'p2', 'x1' UNION ALL
SELECT 3    , 'p3', 'x2' UNION ALL
SELECT 4    , 'p4', 'ht1' UNION ALL
SELECT 5    , 'p5', 'x3' UNION ALL
SELECT 6    , 'p1', 'dm3' UNION ALL
SELECT 6    , 'p6', 'ht2' UNION ALL
SELECT 7    , 'p4', 'dm1'

    where 'x' is some random classification/diagnosis.

    I'd like to pass to UDF (not sure how to):
    "HT codes" - ht1, ht2, ht3, ht4
    "DM codes" - dm1, dm2, dm3, dm4, dm5

    So after executing the query I want p1 and p4 as the result set as they are the only patients who have both, diabetes and hypertension.

    I do know that you can pass a comma separated list and then split the list, but in my case there could be multiple lists I'd like to pass ("HT codes" and "DM codes" for eg) in which case the UDF should do a ...IN list1 AND ...IN list2 type of a join.
     

All Replies

  • Monday, January 21, 2013 7:49 PM
     
     Answered
    Sign In to Vote
    0
    A few comments:
    - trying to make this generic is going to force you to use a stored proc and not a function.  You essentially can't do dynamic queries through T_SQL user-defined functions.
    - I don't think you looked hard enough at using a criteria table, which would make it fairly easy to codify what you're trying to get at.  Using a (permanent) table with a QueryID, listNum, ListValue combination of fields would allow you to create your lists on the fly, and only need to pass in the queryID you want to run.
    - at that point, since you're talking about a variable number of lists to possibly check, you're into Dynamic SQL to do this.

    If you wanted to specify the maximium number of lists you might have, then you could  get away with not going dynamic, which might mean you could use a function to generate the results. That being said - depending on what you do with the patient list after that, it may not be desirable to set this up as a function (since functions like this would return a table variable, which would not tend to behave well if you plan on joining to it in an outer query).        
    • Marked As Answer by Roman32 Tuesday, January 22, 2013 4:41 PM
    •  
     
  • Monday, January 21, 2013 9:06 PM
     
      Has Code
    Sign In to Vote
    0

    Something like this

    create table code( code varchar(20) primary key )
create table code_group (code_group varchar(20) primary key)
create table code_group_code
(
   code varchar(20) references code on delete cascade,
   code_group varchar(20) references code_group on delete cascade,
   constraint pk_code_group_code primary key (code, code_group)
)
create index ix_code_group_code on code_group_code(code_group)

insert into code values ('ht1'),('ht2'),('ht3'),('dm1'),('dm2'),('dm3'),('x1'),('x2'),('x3')


insert into code_group values ('HT codes'),('DM codes')

insert into code_group_code(code,code_group) values 
 ('ht1','HT codes'),('ht2','HT codes'),('ht3','HT codes'),
 ('dm1','DM codes'),('dm2','DM codes'),('dm3','DM codes')
 go
 create table patient_code
 (
   id varchar(10),
   code varchar(20) references code,
   constraint pk_patient_code primary key (id,code)
)
create index ix_patient_code on patient_code(code)

insert into patient_code(id,code)
SELECT 'p1', 'ht2' UNION ALL
SELECT 'p2', 'x1' UNION ALL
SELECT 'p3', 'x2' UNION ALL
SELECT 'p4', 'ht1' UNION ALL
SELECT 'p5', 'x3' UNION ALL
SELECT 'p1', 'dm3' UNION ALL
SELECT 'p6', 'ht2' UNION ALL
SELECT 'p4', 'dm1' 

go
--first a very non-generic query
select id
from patient_code pc
where pc.code in 
  (
    select code 
    from code_group_code
    where code_group = 'HT codes'
  )
intersect
select id
from patient_code pc
where pc.code in 
  (
    select code 
    from code_group_code
    where code_group = 'DM codes'
  )

  --now a more dynamic one where you can pass a list of code groups
  declare @code_groups table (code_group varchar(20));
  insert into @code_groups values ('DM codes'), ('HT codes')
  
  select p.id
  from patient_code p
  join code_group_code c
    on p.code = c.code 
  where c.code_group in (select code_group from @code_groups) 
  group by p.id
  having count(distinct c.code_group) = (select count(*) from @code_groups)

    David

    David http://blogs.msdn.com/b/dbrowne/