Marxist Hacker 42's Journal: Fun with SQL Server 2012

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

[Marxist Hacker 42]

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

[smittyoneeach]

Is there a threat (even a theoretical one) of the server going into an infinite loop based upon what you're doing?
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:

[Marxist Hacker 42]

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:

[smittyoneeach]

Again, invoking a subquery is going to let the database engine know: "OK, get all that stuff first, and THEN work on the rest of the query."
If he simple function call is giving you a more lazy evaluation [wikipedia.org] evaluation, that might explain what's occurring.

Re:

[Marxist Hacker 42]

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:

[smittyoneeach]

Precisely. In lazy evaluation, (and Haskell is the poster child for this) a value is only acquired when needed. So if you write SQL that lets the server off the hook for one fetch, while still adhering to the letter of the law, then (if this theory is true) that's what's happening.

Re:

[Marxist Hacker 42]

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

[Bill Dog]

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:

[Marxist Hacker 42]

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:

[Bill Dog]

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