tvc lists date twice

TomD · November 7, 2019 12:31PM

Ran the new tvc snippet on a simple table with a Birth Date [Date] column and got the following output in the @tmp:

...
Birth Date date NULL,
...

Maybe there need to be a QUOTENAME() somewhere?

TomD · November 7, 2019 12:44PM

Yep, this seems to fix it for me:

'+QUOTENAME(Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name))

Phil_Fact0r · November 8, 2019 5:35PM

This should do it. Sorry about the quotename stuff. I hardly ever seem to use columns with illegal characters in them. I meant to add something clever that added the quotes only when necessary but never got around to it.
This should work nicely
/**
Summary: >
  This is a temporary batch  for creating such things as table variable,
  temporary tables or anything else that needs a column list
Author: Phil Factor
**/
Declare @TheExpressionNVARCHAR(MAX)=
N'$SELECTEDTEXT$'

SELECT 'DECLARE @$NameOfVariable$ table ('+
  Stuff ((SELECT ',
    '+QuoteName(Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name))
     + ' '+ System_type_name + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE ''END+' NULL'
   --+ CASE WHEN collation_name IS NULL THEN '' ELSE ' COLLATE '+collation_name END
   AS ThePath
  FROM sys.dm_exec_describe_first_result_set
  (@TheExpression, NULL, 1)AS f
  -- use  sys.sp_describe_first_result_set for a batch
  LEFT OUTER JOIN
    (SELECT name AS name FROM sys.dm_exec_describe_first_result_set
      (@TheExpression, NULL, 0) WHERE is_hidden=0
     GROUP BY name HAVING Count(*)>1) AS DetectDuplicateNames
  ON DetectDuplicateNames.name=f.name
  WHERE f.is_hidden=0
ORDER BY column_ordinal
FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,1,'')+')'

Sergio R · November 26, 2019 11:02AM

This has also been fixed in version 10.0.1.12389 of SQL Prompt

Sergio R · November 8, 2019 4:31PM

Thanks for your feedback, I have spoken with Phil Factor, the creator of that Snippet who confirms that this can be an issue in columns with illegal characters and that your workaround is a good solution. I am going to escalate this to the development team to see if we can change this.

tvc lists date twice

Best Answers

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk