Catch up on stories from the past week (and beyond) at the Slashdot story archive


Forgot your password?
User Journal

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?


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.

This discussion has been archived. No new comments can be posted.

Fun with SQL Server 2012

Comments Filter:
  • 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).

    • 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.
      • 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.

        • 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 [] evaluation, that might explain what's occurring.
          • 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.

            • 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.
  • 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.

    • 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!).

      • 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

Karl's version of Parkinson's Law: Work expands to exceed the time alloted it.