Journal Journal: Fun with SQL Server 2012 11
I have a Table Valued Function that returns a simple parameterized view. I want to turn that view into a string.
Can anybody tell me why the first query works and the second one doesn't?
DECLARE @JobID INT
DECLARE @strOut VARCHAR(MAX)
SET @JobID=2861
SELECT @strOut =Coalesce(@strOut +',','')+ ISNULL('[' +
MP.ModelPointName + '] int', 'ErrorInFactoryModel int')
FROM (SELECT TOP 800 ModelPointName, Sequence
FROM dbo.GetReferencedModelPointsByJobID(@JobID)
ORDER BY Sequence) MP
WHERE NOT (MP.ModelPointName LIKE '%Ship%'
OR MP.ModelPointName LIKE '%Scrap%')
PRINT @strOut
SET @strOut=NULL
SELECT @strOut =Coalesce(@strOut +',','')+ ISNULL('[' +
MP.ModelPointName + '] int', 'ErrorInFactoryModel int')
FROM dbo.GetReferencedModelPointsByJobID(@JobID) MP
WHERE NOT (MP.ModelPointName LIKE '%Ship%'
OR MP.ModelPointName LIKE '%Scrap%')
ORDER BY Sequence
PRINT @strOut
The 2nd one returns a single field name, the first, returns all the field names.