Is this T-SQL dangerous/not reliable

We are trying to create a string of column names for a
given table. (SQL 2000, SP3a--W2K Server, SP3)
The included code here works well so far, but I remember
reading in the past that the optimizer can sometimes break
this approach to creating strings.
If all column names are not null and the total len(string)
does not exceed varchar(8000), is this construct safe? If
not, why?
TIA, -- Brian
declare @.FldStr varchar(8000)
select @.FldStr = ''
select @.FldStr = @.FldStr + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @.TableName
order by ORDINAL_POSITIONThis behavior isn't documented and also isn't supported. I believe someone
posted an example not too long ago that showed this syntax breaking, but
can't seem to find it on a quick search of google.
Why not return the set of column names to your application, and have the
application assemble them into a string?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
>
>
>|||Thanks, Aaron.
I'm taking it further and doing joins dynamically with
sp_executesql, thus would like to keep it on the backend.
Would sending the field names to a #temp table with an
identity field be a better to go, looping through 1 to n
records to build the string of field names?
I'll also try to find the post you mentioned. I'm very
interested in the behind the scenes stuff that would cause
this to break. If you find it at a later point, I'd
greatly appreciate it if you could forward it to me.
(brianglinebaugh@.yahoo.com)
Thanks very much for your time, -- Brian
>--Original Message--
>This behavior isn't documented and also isn't supported.
I believe someone
>posted an example not too long ago that showed this
syntax breaking, but
>can't seem to find it on a quick search of google.
>Why not return the set of column names to your
application, and have the
>application assemble them into a string?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09c401c3b076$8d083260$a001280a@.phx.gbl...
>> We are trying to create a string of column names for a
>> given table. (SQL 2000, SP3a--W2K Server, SP3)
>> The included code here works well so far, but I remember
>> reading in the past that the optimizer can sometimes
break
>> this approach to creating strings.
>> If all column names are not null and the total len
(string)
>> does not exceed varchar(8000), is this construct safe?
If
>> not, why?
>> TIA, -- Brian
>> declare @.FldStr varchar(8000)
>> select @.FldStr = ''
>> select @.FldStr = @.FldStr + COLUMN_NAME
>> from INFORMATION_SCHEMA.COLUMNS
>> where TABLE_NAME = @.TableName
>> order by ORDINAL_POSITION
>>
>>
>>
>
>.
>|||"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
How about
set @.FldStr = '*'
?
David|||I don't know of a KB that states this but it will fail most of the time in
anything other than a simple select with no order by, join etc. You do not
want to put that code into your production env. Create a cursor and di it
that way if you must have it in a particular order.
--
Andrew J. Kelly
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:3a2c01c3b07f$eef7fab0$a601280a@.phx.gbl...
> Thanks, Aaron.
> I'm taking it further and doing joins dynamically with
> sp_executesql, thus would like to keep it on the backend.
> Would sending the field names to a #temp table with an
> identity field be a better to go, looping through 1 to n
> records to build the string of field names?
> I'll also try to find the post you mentioned. I'm very
> interested in the behind the scenes stuff that would cause
> this to break. If you find it at a later point, I'd
> greatly appreciate it if you could forward it to me.
> (brianglinebaugh@.yahoo.com)
> Thanks very much for your time, -- Brian
>
>
>
> >--Original Message--
> >This behavior isn't documented and also isn't supported.
> I believe someone
> >posted an example not too long ago that showed this
> syntax breaking, but
> >can't seem to find it on a quick search of google.
> >
> >Why not return the set of column names to your
> application, and have the
> >application assemble them into a string?
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> >"Brian" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> >>
> >> We are trying to create a string of column names for a
> >> given table. (SQL 2000, SP3a--W2K Server, SP3)
> >>
> >> The included code here works well so far, but I remember
> >> reading in the past that the optimizer can sometimes
> break
> >> this approach to creating strings.
> >>
> >> If all column names are not null and the total len
> (string)
> >> does not exceed varchar(8000), is this construct safe?
> If
> >> not, why?
> >>
> >> TIA, -- Brian
> >>
> >> declare @.FldStr varchar(8000)
> >> select @.FldStr = ''
> >>
> >> select @.FldStr = @.FldStr + COLUMN_NAME
> >> from INFORMATION_SCHEMA.COLUMNS
> >> where TABLE_NAME = @.TableName
> >> order by ORDINAL_POSITION
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
the optimizer behavior luckily generated a plan for that concatenated the
values in a way you intended. However, this is a risky proposition, since
this is undocumented, inconsistent and thus unreliable. Here are some trials
that can break your luck.
--#1 ( Add a TOP clause)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT TOP 100 PERCENT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#2 ( Add a DISTINCT)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT DISTINCT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#3 ( Add a CROSS JOIN)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + c1.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c1, (SELECT 1) D (n)
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#4 (Use another view)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE TABLE_NAME = 'test'
ORDER BY TABLE_NAME;
SELECT @.FldStr;
--
- Anith
( Please reply to newsgroups only )|||"Anith Sen" wrote
> In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
> the optimizer behavior luckily generated a plan for that concatenated the
> values in a way you intended..
Where I live everyone drives a SUV.I drive a Benz 560 SL.
They keep telling me 'this is a risky proposition, since
it's undocumented, inconsistent and thus unreliable' :~)