Journal Marxist Hacker 42's 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.
Yes, I'm already aware (Score:2)
That I'm running a risk with the first of > 800 Model Points, but in my database, I never exceed 80 modelpoints for a given job ID.
But I still don't understand why the subquery is necessary (in some cases).
Haven't done T-SQL in years (Score:1)
It may be that the database engine requires the subquery to break the possibility of a cycle in the code, and sequence the computation.
Re: (Score:2)
I didn't explain the behavior adequately.
SELECT * FROM dbo.GetReferencedModelPointsByJobID(@JobID)
Returns someplace between 2-56 rows, depending on JobID
The second query does NOT error out, but is not returning a comma delimited string of all rows, but instead, in some cases, is returning only ONE row.
Since I'm using this to build a temp table, it doesn't error out until I attempt to fill columns in the temp table that do not exist.
Re: (Score:1)
If he simple function call is giving you a more lazy evaluation [wikipedia.org] evaluation, that might explain what's occurring.
Re: (Score:2)
I thought the table valued function call was already doing that, but that does point to an answer- perhaps it's only fetching the one row before evaluating the concatenation.
Re: (Score:1)
Re: (Score:2)
The Top 800 is included in the subquery because MS SQL returns an error without it- Can't use an Order By in a subquery without a TOP. 800 just happens to be ~5x the current number of Model Points in the Model Points Table (and thus, this function should NEVER return that many rows).
a thought (Score:1)
In SQL's order of operations, ORDER BY is done after SELECT, where in that 2nd query the string is built up, and then somehow some sorting is supposed to happen. It could be harmless or fouling things up, and it might not be what you want judging from the 1st query where the string is built in Sequence order.
Re: (Score:2)
I do want the string built in Sequence Order.
Here's the weird thing- query #2 has been working for 4 months. Not sure what changed, but the explanation that the rows are being fetched slow makes sense (they're always messing with my server settings!).
Re: (Score:1)
Another thought is this: I've written my share of T-SQL in the same spirit as this. And that is, what I have come to philosophically consider to be doing too much on the database side. An RDBMS's strong suit is retrieving data, not string manipulations. And your requirements for the data to be built into a string and of a certain format is really a business rule, where even if you're not doing a tiered architecture physically, it isn't a best practice to mix business layer concerns into what is logically