How can I get this query to run in a function?

ALTER function [cd].[address_label] (@type varchar(15), @program varchar(5))


returns table
as
return(
---How can I get this query to work in this function?
--Query Start
if @type not in ('primary','client mailing') 
select 
(case when ec.contact_vc is null then cl.firstname_vc+' '+cl.lastname_vc else ec.contact_vc end) as name,
       (case when ea.address1_vc is null then ep.address1_vc else ea.address1_vc end) as address1,
       (case when ea.address1_vc is null then ep.address2_vc else ea.address2_vc end) as address2,
       (case when ea.address1_vc is null then ep.city_vc else ea.city_vc end) as city,
       (case when ea.address1_vc is null then ep.state_c else ea.state_c end) as state,
       (case when ea.address1_vc is null then ep.zip_c else ea.zip_c end) as zip                    

from ar.client cl 
join echo.er_contact ec on ec.linkid_c = cl.uniqueid_c 
                            and ec.source_c = 'client' and (ec.type_c = @type or ec.type_c is null)
                            and (ec.startdate_d <= getdate() or ec.startdate_d is null)
                            and (ec.enddate_d is null or ec.enddate_d > getdate())
left join echo.er_address ep on ep.linkid_c = cl.uniqueid_c 
                            and ep.source_c = 'client' and ep.type_c = 'primary'
                            and (ep.startdate_d <= getdate() or ep.startdate_d is null)
                            and (ep.enddate_d is null or ep.enddate_d > getdate())
left join echo.er_address ea on ea.linkid_c = cl.uniqueid_c and (ea.country_vc = ec.priority_c or ea.country_vc is null)
                            and ea.source_c = 'client' and ea.type_c = @type
                            and (ea.startdate_d <= getdate() or ea.startdate_d is null)
                            and (ea.enddate_d is null or ea.enddate_d > getdate())
where cl.termdate_d is null
and (exists (select 1 from cd.enrollments where (program_c = @program and clientid_c = cl.uniqueid_c and 
                                                 (enddate_d is null or (enddate_d <= getdate())))) 
     or @program = 'all')


	 if @type in ('primary','client mailing')
select 
 cl.firstname_vc+' '+cl.lastname_vc  as name,
       (case when ea.address1_vc is null then ea.address2_vc else ea.address1_vc end) as address,
       --(case when ea.address1_vc is null then ep.address2_vc else ea.address2_vc end) as address2,
        ea.city_vc  as city,
        ea.state_c  as state,
        ea.zip_c  as zip                    

from ar.client cl 
--Took out some fluff because when we look for Primary thats all we want is primary..... and we do not nee contact info for the primary address
 join echo.er_address ea on ea.linkid_c = cl.uniqueid_c --and (ea.country_vc = ec.priority_c or ea.country_vc is null)
                            and ea.source_c = 'client' and ea.type_c = @type
                            and (ea.startdate_d <= getdate() or ea.startdate_d is null)
                            and (ea.enddate_d is null or ea.enddate_d > getdate())
where cl.termdate_d is null
and (exists (select 1 from cd.enrollments where (program_c = @program and clientid_c = cl.uniqueid_c and 
                                                 (enddate_d is null or (enddate_d <= getdate())))) 
     or @program = 'all')

	 --End of Query
)

Open in new window

mguptillAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mguptillAuthor Commented:
One thing I forgot to mention is the Variables are pulled from the App the fires this function off...

I'd like to declare @type = @type etc
0
ste5anSenior DeveloperCommented:
You need a Table-Valued User-Define Function (TVF) instead of a Inline User-Defined Function.

E.g.
ALTER FUNCTION [cd].[address_label]
    (
      @type VARCHAR(15) ,
      @program VARCHAR(5)
    )
RETURNS @Result TABLE
    (
      name NVARCHAR(255) ,
      address1 NVARCHAR(255) ,
      address2 NVARCHAR(255) ,
      city NVARCHAR(255) ,
      [state] NVARCHAR(255) ,
      zip NVARCHAR(255)
    )
AS
    BEGIN
        IF @type NOT IN ( 'primary', 'client mailing' )
            INSERT  INTO @Result
                    ( name ,
                      address1 ,
                      address2 ,
                      city ,
                      [state] ,
                      zip
                    )
                    SELECT  ( CASE WHEN ec.contact_vc IS NULL THEN cl.firstname_vc + ' ' + cl.lastname_vc
                                   ELSE ec.contact_vc
                              END ) AS name ,
                            ( CASE WHEN ea.address1_vc IS NULL THEN ep.address1_vc
                                   ELSE ea.address1_vc
                              END ) AS address1 ,
                            ( CASE WHEN ea.address1_vc IS NULL THEN ep.address2_vc
                                   ELSE ea.address2_vc
                              END ) AS address2 ,
                            ( CASE WHEN ea.address1_vc IS NULL THEN ep.city_vc
                                   ELSE ea.city_vc
                              END ) AS city ,
                            ( CASE WHEN ea.address1_vc IS NULL THEN ep.state_c
                                   ELSE ea.state_c
                              END ) AS state ,
                            ( CASE WHEN ea.address1_vc IS NULL THEN ep.zip_c
                                   ELSE ea.zip_c
                              END ) AS zip
                    FROM    ar.client cl
                            JOIN echo.er_contact ec ON ec.linkid_c = cl.uniqueid_c
                                                       AND ec.source_c = 'client'
                                                       AND ( ec.type_c = @type
                                                             OR ec.type_c IS NULL
                                                           )
                                                       AND ( ec.startdate_d <= GETDATE()
                                                             OR ec.startdate_d IS NULL
                                                           )
                                                       AND ( ec.enddate_d IS NULL
                                                             OR ec.enddate_d > GETDATE()
                                                           )
                            LEFT JOIN echo.er_address ep ON ep.linkid_c = cl.uniqueid_c
                                                            AND ep.source_c = 'client'
                                                            AND ep.type_c = 'primary'
                                                            AND ( ep.startdate_d <= GETDATE()
                                                                  OR ep.startdate_d IS NULL
                                                                )
                                                            AND ( ep.enddate_d IS NULL
                                                                  OR ep.enddate_d > GETDATE()
                                                                )
                            LEFT JOIN echo.er_address ea ON ea.linkid_c = cl.uniqueid_c
                                                            AND ( ea.country_vc = ec.priority_c
                                                                  OR ea.country_vc IS NULL
                                                                )
                                                            AND ea.source_c = 'client'
                                                            AND ea.type_c = @type
                                                            AND ( ea.startdate_d <= GETDATE()
                                                                  OR ea.startdate_d IS NULL
                                                                )
                                                            AND ( ea.enddate_d IS NULL
                                                                  OR ea.enddate_d > GETDATE()
                                                                )
                    WHERE   cl.termdate_d IS NULL
                            AND ( EXISTS ( SELECT   1
                                           FROM     cd.enrollments
                                           WHERE    ( program_c = @program
                                                      AND clientid_c = cl.uniqueid_c
                                                      AND ( enddate_d IS NULL
                                                            OR ( enddate_d <= GETDATE() )
                                                          )
                                                    ) )
                                  OR @program = 'all'
                                )


        IF @type IN ( 'primary', 'client mailing' )
            INSERT  INTO @Result
                    ( name ,
                      address1 ,
                      city ,
                      [state] ,
                      zip
                    )
                    SELECT  cl.firstname_vc + ' ' + cl.lastname_vc AS name ,
                            ( CASE WHEN ea.address1_vc IS NULL THEN ea.address2_vc
                                   ELSE ea.address1_vc
                              END ) AS address ,
                            ea.city_vc AS city ,
                            ea.state_c AS state ,
                            ea.zip_c AS zip
                    FROM    ar.client cl
                            JOIN echo.er_address ea ON ea.linkid_c = cl.uniqueid_c
                                                       AND ea.source_c = 'client'
                                                       AND ea.type_c = @type
                                                       AND ( ea.startdate_d <= GETDATE()
                                                             OR ea.startdate_d IS NULL
                                                           )
                                                       AND ( ea.enddate_d IS NULL
                                                             OR ea.enddate_d > GETDATE()
                                                           )
                    WHERE   cl.termdate_d IS NULL
                            AND ( EXISTS ( SELECT   1
                                           FROM     cd.enrollments
                                           WHERE    ( program_c = @program
                                                      AND clientid_c = cl.uniqueid_c
                                                      AND ( enddate_d IS NULL
                                                            OR ( enddate_d <= GETDATE() )
                                                          )
                                                    ) )
                                  OR @program = 'all'
                                )
        RETURN;
    END;

Open in new window


p.s. please use the Embed Code Snippet button from the toolbar in future posts. Also post formatted code. E.g. using an online formatter like T-SQL Tidy.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mguptillAuthor Commented:
This worked perfectly thank you so much!!!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.