question

braxx avatar image
0 Votes"
braxx asked Viorel-1 commented

create dynamic table based on list

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?








sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


What type does it return?

0 Votes 0 ·

It is mostly nvarchar. There are no aggregations.

0 Votes 0 ·
Viorel-1 avatar image
1 Vote"
Viorel-1 answered Viorel-1 commented

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)


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks, this did the job!

Untill it is a comment under the post I cannot mark it as an answer.
Could you please "Write an Answer" so I could accept it?

0 Votes 0 ·

Converted.

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.