tvc lists date twice

TomD

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

Yep, this seems to fix it for me:

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

Phil_Fact0r

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

This has also been fixed in version 10.0.1.12389 of SQL Prompt

Sergio R

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.