Share via

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.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments
Accepted answer
  1. LiHongMSFT-4306 25,651 Reputation points
    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

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  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,721 Reputation points
    2022-09-21T16:33:48.53+00:00

    Change: 

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

    to: 

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