How to "somewhat" use dynamic UDF 1

[logidude]

Hi all,

Sorry I know it's a long post but I wanted to give as much info as possible.

CLIFF NOTE: Trying to get a table-valued function to return data based on different filters.

This is somewhat of a follow to this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1514866&page=1

In my previous post I was creating a table-valued function to get data depending on some parameters.

Now I decided to get a little funky with that function in hope of being able to reuse it.

What I wanted to do is change my function so I could pass a list of individualIDs instead of an Int. I thought I could do :

create function dbo.GetInfo(@IDsList varchar, @languageID int, @bOrder bit)

    returns table
    as
    return (
    select
        Case When @bOrder = 1
             Then coalesce(firstName, firstNameUNIcode)
             Else coalesce(firstNameUNIcode, firstName)
             END As FirstName,
        Case When @bOrder = 1
             Then coalesce(lastName, lastNameUNIcode)
             Else coalesce(lastNameUNIcode, lastName)

             End As LastName
    FROM
        tblPerson
        LEFT OUTER JOIN tblPersonUNICODE on tblPerson.personID = tblPersonUNICODE.fkPersonID
        AND tblPersonUNICODE.fkLangID = @languageID
    WHERE tblPerson.personID in (@IDsList)
    )

Found out it doesn't work as it can't handle the varchar as I thought it would...it tries converting "1,2,3" to an Int not @p1, @p2, @p3 as expected.

So we move on, I create a stored proc to get my IDs based on a dynamic query...figured if I can run the dynamic query inside the function and store the IDs in a table variable or a temp table I will just need to do:

WHERE tblPerson.personID in (select * from @tblFilter) or
WHERE tblPerson.personID in (select * from #tblFilter)

EPIC FAIL as I can't execute a stored proc. inside a UDF as I found out the hard way.

Other option, build my table variable outside and pass it as a parameter...no go that's only a feature in MS SQL2008 .

So, as a last resort I thought I would take some of the logic out of the T-SQL and use server side language to do the "heavy" stuff.

I figured I would just store the result of the stored proc. that build a result based on dynamic filters inside a temp table but you can't next exec to do this:

...create #table

INSERT #table EXEC GetIndividualsPrimaryDetails param1, param2, param3, param4

Fetch info plus join on the temp table to get the individuals info.

drop table ...

Since GetIndividualsPrimaryDetails use a dynamic select to get the data based on the param I get a nested EXEC error when inserting.

I'm about to give up and write 600ish lines of messy JOINS with aliases to get my data...what is a poor man to do??????? Indexed views are out as data resides in different database.

Thanks.

 

[gmmastros]

Take a look here:

faq183-5207
faq183-6684

In that link, you will see efficient functions for parsing a comma delimited list of values. Some perform better than others, but with limitations. I encourage you to read it all, and then decide for yourself which function to use.

This will allow you to join to the table-valued function and use it to filter your output.

So...

create function dbo.GetInfo(@IDsList varchar[!](8000)[/!], @languageID int, @bOrder bit)

    returns table
    as
    return (
    select
        Case When @bOrder = 1
             Then coalesce(firstName, firstNameUNIcode)
             Else coalesce(firstNameUNIcode, firstName)
             END As FirstName,
        Case When @bOrder = 1
             Then coalesce(lastName, lastNameUNIcode)
             Else coalesce(lastNameUNIcode, lastName)

             End As LastName
    FROM
        tblPerson
        [!]Inner Join dbo.Split(@IDsList, ',') As IdList
          On tblPerson.PersonId = IdList.Value[/!]
        LEFT OUTER JOIN tblPersonUNICODE on tblPerson.personID = tblPersonUNICODE.fkPersonID
    )

Also, do yourself a favor. Anytime you use a varchar, please specify the size. Trust me, if you don't specify the size, you will eventually regret it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[logidude]

George once again, thank your help. This solution worked perfectly. I went with a regular CSV as it was fine for my need but Option 6 is very creative...I've bookmarked it for future reference.