AnsweredAssumed Answered

Dynamic SQL

Question asked by Robert Ensinger on Aug 7, 2017
Latest reply on Aug 10, 2017 by Robert Ensinger

I know this isn't the right place for SQL help but since the finished version of this could do us all some good...

 

I'm working on a Dynamic SQL where one part is failing. I've created a little test case below to demonstrate the fail in hopes that someone can tell me what I'm doing wrong.

 

Here's the hard coded example:

SELECT
TOP 1 (CONVERT(DATE,I.LAST_UPDATED_DATE,106)) LAST_OBJECT_UPDATED_DATE
, 'osumc_est_rev_date' AS ATTRIBUTE_ID
FROM INV_INVESTMENTS I
JOIN ODF_CA_IDEA A ON A.ID = I.ID
WHERE osumc_est_rev_date IS NOT NULL
ORDER BY I.LAST_UPDATED_DATE DESC

If you want to run it, any custom attribute should do. It's telling me the top last updated date for an Idea where a particular custom attribute was used (not null). The answer 11/7/2014 has been validated.

 

Trying to turn this little test harness dynamic.

DECLARE @CustAttr nvarchar(75)
SET @CustAttr = 'osumc_est_rev_date'

SELECT
TOP 1 (CONVERT(DATE,I.LAST_UPDATED_DATE,106)) LAST_OBJECT_UPDATED_DATE
, @CustAttr AS ATTRIBUTE_ID
FROM ODF_CA_IDEA A
JOIN INV_INVESTMENTS I ON A.ID = I.ID
WHERE @CustAttr IS NOT NULL
ORDER BY I.LAST_UPDATED_DATE DESC

The WHERE clause isn't working. 

 

Ideas?

Outcomes