-dynamic- function to convert several rows of a table to a csv list

  • Hi all,

    I'd like to convert several rows of my table to csv list

    create table my_contracts(

    customer_id int,

    contract_id int

    )

    insert into my_contracts values (1,100)

    insert into my_contracts values (1,101)

    insert into my_contracts values (2,102)

    select * from my_contracts

    ---------------------------

    customer_id contract_id

    ---------------------------

    1100

    1101

    2102

    I found some useful postings about this (recursive or via XML). Here astatic solution:

    create FUNCTION my_list_static

    (

    -- Add the parameters for the function here

    )

    RETURNS varchar(1000)

    AS

    BEGIN

    declare @list varchar(1000)

    select @list= isnull(@list + ', ','') + convert(varchar(1000),contract_id) from contracts

    where customer_id= 1 order by contract_id

    RETURN @list

    /*

    select dbo.my_list_static() as list_of_contracts

    */

    END

    GO

    my_list_static() works as it should, I find

    list_of_contracts

    -----------------

    100, 101

    Now I want to create a dynamic function, which can be used in general

    alter FUNCTION my_list_dynamic

    (

    @table varchar(100),

    @field_to_list varchar(100),

    @criteria varchar(100)

    )

    RETURNS varchar(1000)

    AS

    BEGIN

    declare @list varchar(1000)

    declare @sql varchar(1000)

    set @sql='select @list= isnull(@list + '', '','''') + convert(varchar(1000),' + @field_to_list + ')'

    set @sql= @sql + ' from ' + @table

    set @sql= @sql + ' where ' + @criteria + ' order by ' + @field_to_list

    -- this is what I find in @sql:

    --select @list= isnull(@list + ', ','') + convert(varchar(1000),contract_id) from my_contracts where customer_id= 1 order by contract_id

    -- here I try to fetch the value into the variable by exec but it fails!

    exec @sql

    RETURN @list

    END

    GO

    /*

    select dbo.my_list_dynamic('my_contracts', 'contract_id','customer_id= 1') as list_of_contracts

    */

    Any idea how I can resolve this problem?

    Thanks you a lot for each answer...

    Best Regards

    Martin Berkl

  • Take note of the added parentheses around @sql in your EXEC statement...

    Steve

    (aka smunson)

    :):):)

    smerg (7/8/2008)


    Hi all,

    I'd like to convert several rows of my table to csv list

    create table my_contracts(

    customer_id int,

    contract_id int

    )

    insert into my_contracts values (1,100)

    insert into my_contracts values (1,101)

    insert into my_contracts values (2,102)

    select * from my_contracts

    ---------------------------

    customer_id contract_id

    ---------------------------

    1100

    1101

    2102

    I found some useful postings about this (recursive or via XML). Here astatic solution:

    create FUNCTION my_list_static

    (

    -- Add the parameters for the function here

    )

    RETURNS varchar(1000)

    AS

    BEGIN

    declare @list varchar(1000)

    select @list= isnull(@list + ', ','') + convert(varchar(1000),contract_id) from contracts

    where customer_id= 1 order by contract_id

    RETURN @list

    /*

    select dbo.my_list_static() as list_of_contracts

    */

    END

    GO

    my_list_static() works as it should, I find

    list_of_contracts

    -----------------

    100, 101

    Now I want to create a dynamic function, which can be used in general

    alter FUNCTION my_list_dynamic

    (

    @table varchar(100),

    @field_to_list varchar(100),

    @criteria varchar(100)

    )

    RETURNS varchar(1000)

    AS

    BEGIN

    declare @list varchar(1000)

    declare @sql varchar(1000)

    set @sql='select @list= isnull(@list + '', '','''') + convert(varchar(1000),' + @field_to_list + ')'

    set @sql= @sql + ' from ' + @table

    set @sql= @sql + ' where ' + @criteria + ' order by ' + @field_to_list

    -- this is what I find in @sql:

    --select @list= isnull(@list + ', ','') + convert(varchar(1000),contract_id) from my_contracts where customer_id= 1 order by contract_id

    -- here I try to fetch the value into the variable by exec but it fails!

    exec (@sql)

    RETURN @list

    END

    GO

    /*

    select dbo.my_list_dynamic('my_contracts', 'contract_id','customer_id= 1') as list_of_contracts

    */

    Any idea how I can resolve this problem?

    Thanks you a lot for each answer...

    Best Regards

    Martin Berkl

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Hi,

    As you've no doubt guessed - You can't execute a stored procedure from a function:

    Msg 557, Level 16, State 2, Line 1

    Only functions and extended stored procedures can be executed from within a function.

    If I had to fix your problem, I'd probably consider going down the CLR route - Use managed code data-access to build up your CSV, then return that from the assembly? Any CLR experts want to offer help?

    Good luck - Leo.

  • Leo Mepham (7/9/2008)


    Any CLR experts want to offer help?

    No... but you don't need any CLR experts to do this either... Leo... I'm not picking on you but

    I have a bone to pick with anyone that will listen...

    SEE????!!!?!?! Everybody keeps saying "Oh, cursors have their place" or "Oh,

    CLR's have their place"... "You should only use them when no other way is possible..."

    The problem is, PEOPLE WHO DON'T REALLY KNOW SQL ARE GOING TO USE THEM ALL THE

    BLOODY TIME!!!! And they never learn T-SQL in the process...

    If you use a cursor or a While loop or a CLR to get something done, there's a 99.9% chance

    that it's because you don't know what you're doing in T-SQL (yet). Sure, there are some

    routines that traverse databases or tables that might need a cursor or while loop (actually,

    thanks to VARCHAR(MAX), NOT ANY MORE!)... you should probably use a CLR for things like

    "RegEx replace" and maybe some very high level math... but the other 99.9% of the time,

    you're just wrong to use them.

    Take the simple problem posed on this thread...

    Here's some test data...

    [font="Courier New"]--===== Create and populate the test table

    CREATE TABLE MyContracts

    (

    CustomerID INT,

    ContractID INT

    )

    INSERT INTO MyContracts

    (CustomerID,ContractID)

    SELECT 1,100 UNION ALL

    SELECT 1,101 UNION ALL

    SELECT 1,102 UNION ALL

    SELECT 1,103 UNION ALL

    SELECT 1,104 UNION ALL

    SELECT 1,105 UNION ALL

    SELECT 1,106 UNION ALL

    SELECT 1,107 UNION ALL

    SELECT 1,108 UNION ALL

    SELECT 2,202 UNION ALL

    SELECT 3,301 UNION ALL

    SELECT 3,302

    --===== Verify the contents of the test table

    SELECT * FROM MyContracts

    [/font]

    ... and here's the solution...

    [font="Courier New"]--===== Do the concatenation without a CLR!

    SELECT t1.CustomerID ,

    STUFF((SELECT ',' + CAST(t2.ContractID AS VARCHAR(10))

    FROM MyContracts t2

    WHERE t1.CustomerID = t2.CustomerID FOR XML PATH(''))

    ,1,1,'')

    FROM MyContracts t1

    GROUP BY t1.CustomerID[/font]

    Now, somebody tell me why we need a CLR for that? That answer is, because the original

    folks doing the programming didn't have a clue how to do it in T-SQL and instead of trying

    to find out how, the went to the non-database programmers Nirvana known as a CLR.

    And OH so many think a CLR will run quicker... consider this... they don't know how to

    write some T-SQL to begin with so they write some crap T-SQL and then compare that

    against a CLR which may run good even if it too is crap... of course the CLR is going to win!

    You fixed the bloody race! 😉

    Heh... can you tell someone is standing on my last nerve about this very subject today? 😀

    Don't even get me started on people who write code so wide that you always have to

    ^(%*$&$! scroll to the right to read it :hehe:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Martin,

    I also have to say that this is usually a very bad thing to do in SQL Server... most

    will tell you that if you need to do it for the GUI, then do it in the GUI... that is, of

    course, if you have a GUI. 😛

    Other than that, I hope my solution helps you... and don't let people talk you into

    CLR's just because they can't figure it out in SQL without some good amount of

    performance. 😉 And, NO, putting something in a CLR does NOT automatically mean

    that it's going to be faster. In fact, Matt Miller and I did some pretty severe testing

    (I just gotta find that URL again) and we beat the pants off of every CLR problem

    thrown at us with the exception of some very complicated RegEx Replace

    functions... even then, we still came close! :w00t:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, I work with TSQL all day long, and I do read & enjoy your pieces on here - But I don't see that you've solved his problem? :ermm: How would you create the dynamic function in your example? You've done a good example for static CSV creation, but I don't see how that could be converted into a dynamic-column-selecting CSV generator.

    I went down the CLR route because the original poster has painted himself into a corner, and now needs something that should be impossible in standard SQL - He needs to execute dynamic SQL for each row of a resultset. We don't know WHY he needs to do this, so perhaps I shoul have established that in order to find a more compliant solution.

    At my workplace, I don't use the CLR, I tried it for string splitting - We have ended up with lots of sprocs taking CSV lists of params, so I needed a fast splitter. Turned out that although the CLR was slightly faster, because it was opaque the query optimiser ended up mis-guessing the 'estimated rows' so badly that it was faster to go back to the tally table! Sommarskog's pages were invaluable there 😀

    If you have something that fulfills the OP's requirements, some TSQL that fits into a function that can be called with params, that will output CSVs based on column names passed in - I'd be interested in seeing that.

    If the CLR is not acceptable, is the possible sample set small enough that you could make every combination function? So, you'd have fnCSVfromContractID, fnCSVfromCustomerID, fnCSVfromContractIDCustomerID. These could then be selected from a master function fnCSV based on which column names you pass in? Perhaps a little excessive, but it could solve the problem without recourse to the CLR?

    Best wishes - Leo.

  • Leo Mepham (7/10/2008)


    Jeff, I work with TSQL all day long, and I do read & enjoy your pieces on here - But I don't see that you've solved his problem? :ermm: How would you create the dynamic function in your example? You've done a good example for static CSV creation, but I don't see how that could be converted into a dynamic-column-selecting CSV generator.

    OP didn't ask for a dynamic-column-selecting CSV generator... OP asked for something that would create CSV's grouped by EmployeeID... Run my code example and read the OP... it does exactly what was asked.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi all, I'd like to convert several rows of my table to csv list

    my_list_static() works as it should

    Now I want to create a dynamic function, which can be used in general

    alter FUNCTION my_list_dynamic

    (

    @table varchar(100),

    @field_to_list varchar(100),

    @criteria varchar(100)

    )

    Note also that he wants to pass in as a parameter, the column to create the CSV from. That's what was asked. OP - Are you still out there and could you clarify? Do you need to pass the column in as a parameter or was that just an example? In which case has Jeff solved this for you or do need further help?

  • Hi,

    I'm still here (very busy at the moment, so I didn't reply so quickly...). I need to pass column and tablename as parameters, so my problem is not yet solved.

    Of course I could use a static solution, but this was not the first time I had similar problems. So I would really appreciate a solution.

    Thanks

    smerg

  • Hi Smerg,

    If that is the requirement then I really can't see any other way from the CLR. You just can't execute dynamic SQL in a function, so using the CLR as a 'cheat' is all I can think of.

    If you haven't used the CLR its not so bad, hopefully you have some .Net experience?

    Here is a good place to start:

    http://msdn.microsoft.com/en-us/library/ms345135.aspx

    You create a wrapper function in TSQL that accepts your table/column name plus other params as required, then passes these on to the method/class in the assembly you've created. It isn't as hard as it sounds, MSDN simple example hacked up a little to match more what you need:

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    public class CLRFunctions

    {

    [SqlFunction]

    public static SqlString fnCSV(SqlString columnName, SqlString tableName)

    {

    // as usual, connection strings shouldn't be hardcoded for production code

    using(SqlConnection conn = new SqlConnection(

    "server=MyServer; database=AdventureWorks; " +

    "user id=MyUser; password=MyPassword"))

    {

    conn.Open();

    //Build up your CSV-making query here

    SqlCommand cmd = new SqlCommand(

    "SELECT " + columnName + " FROM " + tableName, conn);

    SqlDataReader r = cmd.ExecuteReader();

    string CSVResults = "";

    while(r.Read())

    {

    CSVResults += r["" + columnName + ""].ToString() + ",";

    }

    }

    return CSVResults;

    }

    }

    So, in the above, you'd pass in the parameters, construct the query as if you were using dynamic SQL, then use the r.Read() loop to concatenate your CSV, which the assembly then returns to the SQL function, which returns it to the row in question.

    By the way - The above is not tested or compiled, it's just to give you an example of the level of difficulty in dealing with the CLR - How is your .Net coding? I'm sure you'll be fine constructing the dynamic SQL though inside the function though, just add extra parameters to the above class and build the query as required.

    To get the CLR up & running, get your class written then compile into a DLL using either Visual Studio, or CSC.exe (http://msdn.microsoft.com/en-us/library/78f4aasd(VS.80).aspx). Load the DLL into SQL Server and create the TSQL 'wrapper' for the assembly method (http://www.sqldbatips.com/showarticle.asp?ID=22).

    Undoubtedly, this is not an ideal solution - Is there any other way you could approach this? Consider the usual methods in SQL Server - Constructing a function that can do anything means there is no way for SQL Server to optimise it - So performance will always be terrible.

    Do take a look at http://www.sommarskog.se/dynamic_sql.html if you can. His site is full of good TSQL theory & tests, perhaps there is a way to solve your problem without recourse to dynamic SQL or the CLR, but without knowing your situation/requirements/tables etc there's not much else I can suggest.

    Let me know if any of this helps, or if there's anything else you'd like to add?

    Best of luck, now I'd better get out of here before Jeff comes to shout at me again 🙁

  • I wonder why people say it is IMPOSSIBLE to call a stored procedure from a function. It cannot be done directly, but, indirectly, YES it is POSSIBLE. I just ran through the Blog of Denis Gobo illustrating the trick to do it.

    if your table data is not HUGE, it can be done easily.

    Here is the Code to call sp from a UDF...

    USE database

    go

    CREATE TABLE LogMeNow (SomeValue varchar(50), SomeDate datetime default getdate())

    go

    --Here is the proc

    CREATE PROC prLog

    @SomeValue varchar(50)

    AS

    INSERT LogMeNow (SomeValue) VALUES(@SomeValue)

    go

    --And here is the function

    Alter FUNCTION fnBla(@id int)

    RETURNS varchar(max)

    AS

    BEGIN

    DECLARE @SQL varchar(500)

    SELECT @SQL = 'osql -SATIFULLAH -E -q "exec dbs..prLog ''fnBla''"'

    --print @SQL

    EXEC master..xp_cmdshell @SQL

    RETURN @SQL

    END

    --Now call the function a couple of times

    SELECT dbo.fnBla(1)

    SELECT dbo.fnBla(2)

    SELECT dbo.fnBla(4)

    plz check the following link from where I took the Code;

    http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx">

    http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx

    OP can use this technique to accomplish his/her goal easily.

    Enjoy T-SQL...:D

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Nicely found. I doubt either of our solutions are ideal as far as performance and security goes, but it's good to find two solutions to a problem that intially seemed impossible.

    Ideally the OP should redesign the table structure so that things like this aren't neccessary, but I know in the real world that's not always feasible 🙂

    Looks like Atif's solution will be easiest for you to implement then Smerg, good luck!

    PS: Do read up on the security you should implement with xp_cmdshell - It does have a possible downside.

  • Sorry... I just couldn't imagine why anyone would want a dynamic solution for this... now I see one (pass multiple different CSR parameter lists back to a GUI). Still, the need for doing that particular task should not occur that often and a dedicated UDF for when it does happen just doesn't seem all that unreasonable.

    I don't have the ability to run CLR's on my box... it would be interesting if someone actually made a dynamic CLR and checked the performance against a "hard coded" UDF to compare performance. I can help provide a test table to run it against if anyone is interested.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Leo Mepham (7/10/2008)


    Jeff, I work with TSQL all day long, and I do read & enjoy your pieces on here - But I don't see that you've solved his problem? :ermm: How would you create the dynamic function in your example? You've done a good example for static CSV creation, but I don't see how that could be converted into a dynamic-column-selecting CSV generator.

    Wow... I owe you an appology, Leo. I must not have had enough coffee when first responding to this... I couldn't understand the need for a UDF when a result set was so simple... now I get it and I agree... I didn't come close.

    Thanks for the feedback, Leo.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • No problem Jeff, I figured you may have skim-read it. I've knocked up a working version of the CLR assembly, some test data is as follows:

    Firstly, the working code:

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    public class CLRFunctions

    {

    [SqlFunction(DataAccess=DataAccessKind.Read)]

    public static SqlString fnCSV(SqlString columnName, SqlString tableName, SqlString whereClause)

    {

    //Declare results CSV

    SqlString CSVResults = "";

    //Create Query

    string sqlQuery = "SELECT " + columnName.ToString() + " FROM " + tableName.ToString();

    //Add where clause if applicable

    if (!String.IsNullOrEmpty(whereClause.ToString()))

    sqlQuery += " " + whereClause.ToString();

    //Use the current connection

    using (SqlConnection conn = new SqlConnection("Context Connection=true"))

    {

    conn.Open();

    SqlCommand sqlCommand = new SqlCommand(sqlQuery, conn);

    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

    while (sqlDataReader.Read())

    {

    CSVResults += sqlDataReader[columnName.ToString()].ToString() + ",";

    }

    //Trim trailing comma

    if (CSVResults.ToString().Length > 0)

    CSVResults = CSVResults.ToString().Substring(0, CSVResults.ToString().Length - 1);

    }

    return CSVResults;

    }

    }

    I've compiled this into a DLL & loaded onto my test database.

    Next create the TSQL function:

    USE [Work]

    GO

    /****** Object: UserDefinedFunction [dbo].[fnCSV] Script Date: 07/11/2008 15:30:04 ******/

    CREATE FUNCTION [dbo].[fnCSV](@columnName [nvarchar](4000), @tableName [nvarchar](4000), @whereClause [nvarchar](4000))

    RETURNS [nvarchar](max) WITH EXECUTE AS CALLER

    AS

    EXTERNAL NAME [CLRCSV].[CLRFunctions].[fnCSV]

    Lastly, a table with some test data:

    CREATE TABLE TestData

    (

    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    DataString NVARCHAR(8) NOT NULL

    )

    DECLARE @i INT

    SET @i = 10000

    WHILE @i > 0

    BEGIN

    INSERT INTO TestData(DataString)

    SELECT LEFT(NEWID(),8)

    SET @i = @i-1

    END

    Apologies for using a loop Jeff 😉

    So, now we have everything in place, some time trials: (Note, all trials on my workstation - Intel Core 2 @ 1.9GHz, 2GB ram, SQL Server 2005 V 9.0.3068)

    SET STATISTICS TIME ON

    SELECT dbo.fnCSV('DataString','TestData','WHERE ID < 10')

    SET STATISTICS TIME OFF

    Results: 7ms

    SET STATISTICS TIME ON

    SELECT dbo.fnCSV('DataString','TestData','WHERE ID < 100')

    SET STATISTICS TIME OFF

    Results: 17ms

    SET STATISTICS TIME ON

    SELECT dbo.fnCSV('DataString','TestData','WHERE ID < 1000')

    SET STATISTICS TIME OFF

    Results: 59ms

    SET STATISTICS TIME ON

    SELECT dbo.fnCSV('DataString','TestData','WHERE ID < 10000')

    SET STATISTICS TIME OFF

    Results: 2463ms - FAIL - No results! Perhaps some string overflow somewhere? It would have been 10000 * 8 characters, so perhaps that's the reason :ermm:

    Lastly, can we use this function properly in a query?

    Jeff, in your honour, here's a triangular RBAR done in the CLR:

    SET STATISTICS TIME ON

    SELECT

    ID,

    dbo.fnCSV('DataString','TestData','WHERE ID < ' + CAST(ID AS NVARCHAR(100))) AS OutputCSV

    FROM

    TestData

    WHERE

    ID < 100

    SET STATISTICS TIME OFF

    Results: 310ms

    Lastly, a major test, all 10,000 rows in a triangular RBAR:

    SET STATISTICS TIME ON

    SELECT

    ID,

    dbo.fnCSV('DataString','TestData','WHERE ID < ' + CAST(ID AS NVARCHAR(100))) AS OutputCSV

    FROM

    TestData

    SET STATISTICS TIME OFF

    Results: Had to be cancelled at around 5 minutes, it was up to row 3,000 of 10,000 and slowing my machine to a halt 🙁

    DLL attached in case Smerg still needs it.

    Best wishes all & have a great weekend - Leo

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply