SQLTeam.com | Weblogs | Forums

SQL Server 2008 vs 2014


#1

Hi

I have the following script that works on a server that has SQL Server 2014 but not on a server that has SQL Server 2008. Can anyone help me understand? Is this a SQL Server version issue or a permission issue that I need?

Thank You

SELECT
--'--' + OBJECT_NAME(aFKC.Referenced_Object_ID) + CHAR(13) + CHAR(10) +
--'--' + OBJECT_NAME(aFKC.Parent_Object_ID) + CHAR(13) + CHAR(10) +
'SELECT ''' + OBJECT_NAME(aFKC.Referenced_Object_ID) + ' ***** ' + OBJECT_NAME(aFKC.Parent_Object_ID) + 
''', aParent.*, ''\\\\\*\\\\\'' AS [SEPARATOR], aChild.* FROM ' + OBJECT_NAME(aFKC.Referenced_Object_ID) + ' aParent
INNER JOIN ' + OBJECT_NAME(aFKC.Parent_Object_ID) + ' aChild ON aChild.' + COL_NAME(aFKC.Parent_Object_ID, aFKC.Parent_Column_ID) + ' = aParent.' + COL_NAME(aFKC.Referenced_Object_ID, aFKC.Referenced_Column_ID) + CHAR(13) + CHAR(10) +

IIF (LEN(@vValue) > 0, 
'WHERE' + CHAR(13) + CHAR(10) +
'(
aParent.' + COL_NAME(aFKC.Referenced_Object_ID, aFKC.Referenced_Column_ID)	+ ' IN (' + @vValue + ')' + CHAR(13) + CHAR(10) +
'OR aChild.' + COL_NAME(aFKC.Parent_Object_ID, aFKC.Parent_Column_ID)		+ ' IN (' + @vValue + ')' + CHAR(13) + CHAR(10) +
')' + CHAR(13) + CHAR(10) + '-----------------------------------'
, '')	AS Dynamic_SQL
	--, aFKC.*

FROM SYS.Foreign_Key_Columns aFKC		--SYS.SysForeignKeys

#2

"IIF" was first introduced in 2012 version.

Use "case" instead. Something like:

case
   when len(vValue)>0
   then 'WHERE' + CHAR(13) + CHAR(10)
       +'(aParent.' + COL_NAME(aFKC.Referenced_Object_ID, aFKC.Referenced_Column_ID)
       +' IN (' + @vValue + ')' + CHAR(13) + CHAR(10)
       +'OR aChild.' + COL_NAME(aFKC.Parent_Object_ID, aFKC.Parent_Column_ID)
       +' IN (' + @vValue + ')' + CHAR(13) + CHAR(10)
       +')' + CHAR(13) + CHAR(10)
       +'-----------------------------------'
   else ''
end

#3

Thank You BitsMed. You solved it.

You left out an "@" in len(vValue)>0 but regardless, I understood your information. Have a good weekend.