create dynamic table based on list

Question

I have a SQL function (let's call it MyFunction) which retrives a list of column's names. The output is like col1,col2,col3...
The number of columns may vary. Minimum is one but can be much more.

With the use of this function I'd like to create a stored procedure which when executed, creates a dataset with distinct values for col1, col2, col3...

Something like:

select distinct
    col1,
    col2,
    col3
    ....
from MyTable

It's like a dynamic dataset, which can have different number of columns.
How to fetch this function to the stored procedure?

Answer 1

If the function returns an nvarchar(max) which contains 'col1, col2, col3', then pass it to stored procedure as a parameter called "@columns", where you can build and execute a dynamic query like this:

declare @q as nvarchar(max) = concat('select distinct ', @columns, ' from MyTable')
exec (@q)

Answer 2

You would need to use dynamic SQL, but I am not sure that this should be done at all. Beware that dynamic SQL is an advanced feature and not really apt for inexperienced users. I have an article The Curse and Blessings of Dynamic SQL where you can learn how to write dynamic SQL, when to use it - and when not to.