locked
how to pass t he columns in the coalesce function RRS feed

  • Question

  • how to pass the columns in the coalesce function

    For example i have 2 tables

    table1 - col1 ,col2,col 3

    table2- col1,col2,col3,col4

    I want to use same query where coalesce function can be used and the no.of columns can passed into coalesce function dynamically

    please let me if there is any way

    Regards
    Santosh
    Friday, October 9, 2009 6:42 PM

Answers

  • You can do this pretty easily; however, I do agree with Varsham.  You should not use dynamic sql unless you absolutely need to.  Typically, the more flexible code logic is, the worse performing it is. 

    If you must do this, I have posted a sample below.

    DECLARE @cols VARCHAR(8000),
    		@sql NVARCHAR(4000)
    		
    --Set initial column string
    SET @cols = 'col1,col2,col3,col4'
    --Replace cols with Coalesce
    SET @cols = 'Coalesce(' + REPLACE(@cols,',','), Coalesce(') + ')'
    
    SET @sql = 'SELECT ' + @cols + ' FROM MyTable'
    
    PRINT @sql
    --Execute sql
    --EXEC sp_executesql @sql


    http://jahaines.blogspot.com/
    • Marked as answer by Zongqing Li Friday, October 16, 2009 8:43 AM
    Friday, October 9, 2009 8:49 PM

All replies

  • If I understand your requirements, you can't do that without using dynamic sql but I won't recommend using dynamic sql unless you really need it and you understand the issues associated with dynamic sql usage. Check this link for more info:
    http://www.sommarskog.se/dynamic_sql.html
    Basically every time you have to create the SELECT statement as a string and then execute it. Be aware that the column types have to be compatible unless you do an explicit conversion.

    Thanks,

    Varsham Papikian, New England SQL Server User Group Executive Board, USA
    New England SQL Server User Group; My LinkedIn page
    Please remember to click the "Mark as Answer" button if a post helps you!
    Friday, October 9, 2009 8:32 PM
  • You can do this pretty easily; however, I do agree with Varsham.  You should not use dynamic sql unless you absolutely need to.  Typically, the more flexible code logic is, the worse performing it is. 

    If you must do this, I have posted a sample below.

    DECLARE @cols VARCHAR(8000),
    		@sql NVARCHAR(4000)
    		
    --Set initial column string
    SET @cols = 'col1,col2,col3,col4'
    --Replace cols with Coalesce
    SET @cols = 'Coalesce(' + REPLACE(@cols,',','), Coalesce(') + ')'
    
    SET @sql = 'SELECT ' + @cols + ' FROM MyTable'
    
    PRINT @sql
    --Execute sql
    --EXEC sp_executesql @sql


    http://jahaines.blogspot.com/
    • Marked as answer by Zongqing Li Friday, October 16, 2009 8:43 AM
    Friday, October 9, 2009 8:49 PM