dynamic sql not display is null issue ?

ahmed salah 3,216 Reputation points
2022-09-21T14:02:47.953+00:00

i work on sql server 2019 is null not display on dynamic sql
what i try is

 declare @s2 varchar(max) = ''  
  set @s2 =--'select FROM Extractreports.dbo.FinalReportDashBoardNewReplacement WITH(NOLOCK)'  
   concat(   
     'select ',  
         STUFF((  
             SELECT ',' + ' + isnull(name,'') + '  
                 FROM Extractreports.sys.columns  
                 WHERE [object_id] = OBJECT_ID(N'Extractreports.dbo.FinalReportDashBoardNewReplacement')  
             FOR XML PATH('')  
             ), 1, 1, ''),'FROM Extractreports.dbo.FinalReportDashBoardNewReplacement WITH(NOLOCK)')  
			 select @s2  

issue when see result it show as

select  + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + , + isnull(name,'') + FROM Extractreports.dbo.FinalReportDashBoardNewReplacement WITH(NOLOCK)  

i need instead of name column name display
but this not happen

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,895 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. CosmogHong-MSFT 23,796 Reputation points Microsoft Vendor
    2022-09-22T02:23:00.467+00:00

    Hi @ahmed salah
    Try the following modifications:

    1. Change SELECT ',' + ' + isnull(name,'') + ' to SELECT ',' + 'isnull(' + name + ','''') '
    2. Add one space before FROM Extractreports.dbo.FinalReportDashBoardNewReplacement WITH(NOLOCK) to avoid syntax error. declare @s2 varchar(max) = ''
      set @s2 =--'select FROM Extractreports.dbo.FinalReportDashBoardNewReplacement WITH(NOLOCK)'
      concat('select ',
      STUFF((
      SELECT ',' + 'isnull(' + name + ','''') '
      FROM Extractreports.sys.columns
      WHERE [object_id] = OBJECT_ID(N'Extractreports.dbo.FinalReportDashBoardNewReplacement')
      FOR XML PATH('')
      ), 1, 1, ''),' FROM Extractreports.dbo.FinalReportDashBoardNewReplacement WITH(NOLOCK)')
      select @s2

    Best regards,
    LiHong

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. HUNG Tran 101 Reputation points
    2022-09-21T16:24:50.727+00:00

    Please try with

    declare @s2 varchar(max) = ''  
       set @s2 =--'select FROM Extractreports.dbo.FinalReportDashBoardNewReplacement WITH(NOLOCK)'  
        concat(   
          'select ',  
              STUFF((  
                  SELECT ',' +  isnull(name,'')   
                      FROM sys.columns  
                      WHERE [object_id] = OBJECT_ID(N'Extractreports.dbo.FinalReportDashBoardNewReplacement')  
                  FOR XML PATH('')  
                  ), 1, 1, ''),'FROM Extractreports.dbo.FinalReportDashBoardNewReplacement WITH(NOLOCK)')  
                  select @s2  
    
    1 person found this answer helpful.

  2. Tom Phillips 17,716 Reputation points
    2022-09-21T16:33:48.53+00:00

    Change:

    ...  
    SELECT ',' + ' + isnull(name,'') + '  
    ...  
    

    to:

    ...  
    SELECT ',' + ' + isnull(' + name + ','') + '  
    ...  
    
    0 comments No comments