Check String with Multiple Column Results

Question

I am trying to condense what I already have in a scalar function. In a table, PERSONS, I have multiple people with multiple roles in a department. Table PERSONS looks like this:

id      name            dept         role     
-------------------------------------------
1       John       studentaffairs    Coder    
2       Joe        studentaffairs    Intern
3       Charlie    studentaffairs     Dist
4       Dan        studentaffairs     Dist
5       Rita       studentaffairs     Lead
6       Nuna       studentaffairs     Lead

My function identifies who the person is (the INPUT) and returns a compact view of generally what role they have in the department.

alter function get_simplifiedrolesfordistribution(@name nvarchar(30))
returns varchar(max)
As
Begin
    DECLARE @AudienceTag varchar(255);

    select @AudienceTag = ('studentaffairs, 
                            studentaffairs-'+ IIF(c.role = 'Coder', 'general', '')+
                                              IIF(c.role = ' Volunteer','general','')+
                                              IIF(c.role = ' Intern','summer','')+
                                              IIF(c.role = ' Dist','seasonal','')+
                                              IIF(c.role = ' Lead','manager','')
                          )
    from PERSONS c
    where c.name = @name ;

    Return @AudienceTag;
End;

Testing case:

INPUT - Nuna

Result - studentaffairs, studentaffairs-manager

My question: Is there a simplified way of developing the scalar function in sql? I have multiple tables like the one above that are way bigger. It is possible to type up all the different roles and fitting them to a general category, but it't not ideal.

Thanks for any help.

Can you just create a lookup table the role to a general category? Then you would just join to it. — Isaac
– Isaac, Commented Mar 12, 2020 at 17:21

Yogesh Sharma · Accepted Answer · 2020-03-12 17:29:12Z

1

Simple CASE expression would do the trick :

CONCAT('studentaffairs', dept,
        CASE WHEN c.role IN ('Coder', 'Volunteer') THEN 'general'
             WHEN c.role = 'Intern' THEN 'summer'
             WHEN c.role = 'Dist' THEN 'seasonal'
             WHEN c.role = 'Lead' THEN 'manager'
        END
      )

Then use APPLY :

SELECT c.*, cc.AudienceTag
FROM PERSONS c CROSS APPLY
    ( VALUES (CONCAT('studentaffairs ,', 'studentaffairs - ', 
                      CASE WHEN c.role IN ('Coder', 'Volunteer') THEN 'general'
                           WHEN c.role = 'Intern' THEN 'summer'
                           WHEN c.role = 'Dist' THEN 'seasonal'
                           WHEN c.role = 'Lead' THEN 'manager'
                      END
                    )
             )
    ) cc(AudienceTag)
WHERE c.name = @name;

edited Mar 12, 2020 at 17:29

answered Mar 12, 2020 at 17:19

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pty Over a year ago

Not sure why I get this error: 'Select statements included within a function cannot return data to a client' but when I create this as its own query then it works fine

pty · Accepted Answer · 2020-03-12 19:22:40Z

0

@YogeshSharma has got a good answer but if you having problems, like me, with the scalar function giving the error message 'Select statements included within a function cannot return data to a client', then another alternative:

SELECT @AudienceTag = (CONCAT('studentaffairs ,', 'studentaffairs - ', 
                       CASE WHEN c.role IN ('Coder', 'Volunteer') THEN 'general'
                            WHEN c.role = 'Intern' THEN 'summer'
                            WHEN c.role = 'Dist' THEN 'seasonal'
                            WHEN c.role = 'Lead' THEN 'manager'
                        END
                              )
                       )
    FROM PERSONS c 
    WHERE c.name = @name;

answered Mar 12, 2020 at 19:22

pty

1217 bronze badges

1 Comment

Yogesh Sharma Over a year ago

. . . You can create a view with apply. In-fact, if this only used in the query then apply would be more performant.

Collectives™ on Stack Overflow

Check String with Multiple Column Results

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related