create dynamic table based on list

braxx 426 Reputation points
2021-05-20T13:52:08.763+00:00

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?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,524 questions
{count} votes

Accepted answer
  1. Viorel 110.1K Reputation points
    2021-05-21T09:03:36.803+00:00

    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)
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 99,461 Reputation points MVP
    2021-05-20T22:05:54.52+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments