|
|
Posted by Amb on 10/02/82 11:47
I was the person that originally showed this method to Malc. THis was
used at a company I worked at previously. After talking with my old
work mates, I recieved this information from the guy who first came up
with it: Ill quote it direct from an MSN chat log with the names
changed.
[MSN Log]
Me: I dont suppose you have any opinion on this <URL to this newsgroup>
Me: I suggested it as an answer to a problem for our DBA and now he
wants to know if its a hack fix or predictable known SQL server
behaviour
GN: Just a sec will have a look
GN: Its not a hack. It is designed this way. They talked about it
Tech-Ed.
GN: Eg: To get the balance in a banking app.
create table Test
(
Client varchar(10),
Bal money
)
GO
insert into test values ('1',10)
GO
declare @bal money
declare @addition money
select @addition = 100
update test set @bal = bal = bal + @addition
select @bal
[/end]
The above example is clearly more advanced than the first, but it
works. The thing is that if it isnt supported, then why is it being
taught at the Tech Ed conferences? GN went on to specify that the
method as shown above in Malc's post was taught as a way of doing a
single field pivot.
Erland Sommarskog wrote:
> Malc (M.B.Pinfold@massey.ac.nz) writes:
> > Can somebody please tell me whether the following syntax is supported
> > or whether it's a "feature" that will someday stop working. It works in
> > both SQL Server 2000 and 2005 at the moment.
> >
> > declare @var varchar(1000)
> > set @var = ''
> > select @var = @var + colx from some_table where col1 = some_value
>
> The correct result of this operation is undefined. You may get a
> concatenated list, or only a single value.
>
> I recommend against using this.
>
> There is a supported way to do this on SQL 2005 with help of FOR XML PATH.
> Unfortunately, I have the sample code at home only, so I cannot post it.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|