Unexplained Phenomenom with recursive Variable

Question

Post reply

Unexplained Phenomenom with recursive Variable

YSLGuru

SSC-Insane

Points: 21669
More actions
November 20, 2009 at 3:49 pm

#214042

I don;t know what the term is for doing the eblow operation but I do know it's valid but for some reason it's not working, at least not consistently.
HERE IS A Sample of what Im trying:
DECLARE @sMyVal VARCHAR(8000)
SELECT @sMyVal = ''
SELECT @sMyVal = @sMyVal + IsNull(A.sCol1,'') + ';'
FROM TABLE A
WHERE A.iRowID Between 1 and 100
SELECT @sMyVal
Assuming there are 100 rows in the TABLE then the above should produce a string of text delimited by the ';' and yet that is not what happens. I end up with only 1 value from the 100 that should appear.
If I change the A.sCol1 to a literal value, say the letter 'X' then what I get in th end is a string of 'X', 100 of them to be exact. delimited with the ';'.
I would simply ask why is this not working but whats really weird is it did work and then for some reason it stopped just 10 minutes later and I have no idea why. When I first had problems with this I looked an existing UDF that does this same kind of string building in a UDF and I broke it down piece by piece till it was using my table & columns. The thing worked so I assumed I must have just missed something in my orginal code.
I then save this working code to a file. I revisit this not 10 minutes later and now when I run the code that worked before I once again get a query that retruns 1 value. I have checked to make sure that I should get a bunch of rows and when I switch back to appending a literal character that is what I get.
Any ideas on what may be going on because this is not only highly frustrating since it is something that just stopped working for no apparent reason but also because I know it should work.
Kindest Regards,
Just say No to Facebook!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 1

What do you get for the following?

SELECT COUNT(*)

FROM TABLE A

WHERE A.iRowID Between 1 and 100

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

YSLGuru SSC-Insane Points: 21669 More actions · Answer 2

Jeff Moden (11/20/2009)
What do you get for the following?
SELECT COUNT(*)
FROM TABLE A
WHERE A.iRowID Between 1 and 100

If I substitue TABLE A for a real table I get 100 (The table I used has sequential IDS starting at 1 and incrementing by 1).

I just retested this and now the darn thing is work again. Go figure.

Thanks Jeff

Kindest Regards,

Just say No to Facebook!