Using dynamic SQL to create single 'cell' string. Returning this string via SELECT clause

Question

The following should ultimately return a single scalar value that is a string separated list of account numbers for supplier with ID 1179. With the LEFT function, this works as intended. Without the LEFT function, I see only 'Command(s) completed successfully'. I've looked at the QUOTENAME function documentation and it says it is only capable of returning a 128 character string. Is that why I can't see the full list of accounts (easily in excess of 128 characters)? Is there some way to achieve what I want using another method?

Code:

DECLARE @str1 VARCHAR(MAX)
DECLARE @str2 VARCHAR(MAX)

SET @str1 = LEFT(STUFF(
(
    SELECT DISTINCT ', ' + cpl.clplAcNo
    FROM tblSuppliers s
    JOIN tblClientPriceLists cpl ON (cpl.clplSupplierId = supRowID)
    WHERE s.supRowID = 1179
        AND clplAcNo IS NOT NULL AND clplAcNo <> ''
    FOR XML PATH('')
), 1, 1, ''), 300)

SET @str2 = 'SELECT ' + QUOTENAME(@str1, CHAR(39))

EXEC(@str2)

There are all sorts of things that can go wrong with dynamic SQL, it's best used only where you have no alternative. The example above looks like it can be written as a simple SELECT unless I'm missing something. — Rhys Jones
– Rhys Jones, Commented Jan 27, 2015 at 10:39
I think it was me who was missing something - thanks for your time Rhys. — hwilson1
– hwilson1, Commented Jan 27, 2015 at 12:02

Christian Barron · Accepted Answer · 2015-01-27 10:51:10Z

1

Why not just do:

Select SupplierID + ',' anothercolumn + ',' + LEFT(STUFF(
(
SELECT DISTINCT '', '' + cpl.clplAcNo
FROM tblSuppliers s
JOIN tblClientPriceLists cpl ON (cpl.clplSupplierId = supRowID)
WHERE s.supRowID = 1179
    AND clplAcNo IS NOT NULL AND clplAcNo <> ''''
FOR XML PATH('''')
), 1, 1, ''''), 300)

edited Jan 27, 2015 at 10:51

answered Jan 27, 2015 at 10:20

Christian Barron

2,7551 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hwilson1 Over a year ago

I need more than just the single, comma separated scalar, sorry I didn't make that clear. I also need, on the same row, the supplierID and some other values that I have yet to pick up. The SET @str2 = 'SELECT ' + QUOTENAME(@str1, CHAR(39)) part of the code will ultimately contain a FROM clause.

Christian Barron Over a year ago

@hwilson1 Is the edited what you're looking for? Not sure you need it in dynamic SQL, depends on if you're adding in variables to your select or not. The above will perform the xml path adding on the supplier ID to the single scalar value. As you can see to add others just concatenate them on. I added the commas in case you wanted them but obviously you can take them out.

hwilson1 Over a year ago

that's exactly what I wanted, thanks Christian. The only context in which I've used FOR XML to return a comma separated list was in creating dynamic pivots so I was steering my query towards a similar structure. Makes perfect sense that you'd just use a nested (SELECT ... ) to return a scalar in the outer query SELECT clause

Christian Barron Over a year ago

@hwilson1 No problem, if you are happy with the answer would you mind accepting it so that others know it has been answered? It will also give you some reputation I believe. Thanks

Rhys Jones · Accepted Answer · 2015-01-27 10:49:28Z

See the MSDN docs for QUOTENAME, note the sentence about the first parameter "Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL."

As mentioned above, dynamic SQL is best avoided if you can. You could use this instead of dynamic SQL, and you can easily add a FROM clause to bring back other data.

SELECT (LEFT(STUFF((SELECT DISTINCT ', ' + cpl.clplAcNo
                    FROM tblSuppliers s
                    JOIN tblClientPriceLists cpl ON (cpl.clplSupplierId = supRowID)
                    WHERE s.supRowID = 1179
                        AND clplAcNo IS NOT NULL AND clplAcNo <> ''
                    FOR XML PATH('')
                   ), 
                   1, 1, ''
                  ), 300
            )
       ) as AcNos

Collectives™ on Stack Overflow

Using dynamic SQL to create single 'cell' string. Returning this string via SELECT clause

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related