Why won't this query return data?

Question

Post reply

Why won't this query return data?

jshahan

SSCarpal Tunnel

Points: 4622
More actions
October 24, 2012 at 4:30 pm

#269964

-- create the tally table
SELECT TOP (30)
IDENTITY(INT,0,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
-- Add an index
ALTER TABLE #Tally
ADD CONSTRAINT PK_Tally_N2
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
-- strip the commas and keep the string horizontal
declare @datastring varchar(8000),
@datastring2 varchar(8000)
set @datastring = 'A,B,C,D,E,F,G,H,I,J,K'
set @datastring2 = ''
-- Why doesn't the select directly below return anything?
select @datastring2 = @datastring2 + substring(@datastring,N,1)
from #tally
where n <=len(@datastring)
and substring(@datastring,N,1) <> ','
-- this one returns the data
select @datastring2 as CleanString
Hello all
I'm putting together some materials to demonstrate some of the uses for tally tables (thanks Jeff 🙂 )
and stumbled across a curious issue with the query above that "stuffs" @datastring2.
It doesn't directly return any data (doesn't have to really) but I can't explain why and I was hoping someone could explain that to me.
This is not urgent in any way. I'm just trying to keep learning from you wonderful folks

Viewing 5 posts - 1 through 4 (of 4 total)

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

Are you talking about the following not returning any data?

select @datastring2 = @datastring2 + substring(@datastring,N,1)

from Tally

where n <=len(@datastring)

and substring(@datastring,N,1) <> ','

its because you are actually assigning the output of the query to @datastring2. when you assign a value to a variable using SELECT you get the last value returned from the query (When working with a scalar variable). your query assigns @datastring2 + the substring to the variable @datastring2 which builds the string you want to return.

to demonstrate the principal i will use integers and my tally table

DECLARE @demo INT = 0

SELECT @demo = @demo + N

FROM Tally

WHERE N <= 10

SELECT @demo

when you run your query you are "Adding" (Concatenating) the variable @datastring2 with the substring so instead of a value of 1+2+3+4+5+6+7+8+9+10 (55) you get your completed string assigned to your variable. once you have assigned a value to your variable the final SELECT @WhatEver returns the value.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

subbu1 Hall of Fame Points: 3281 More actions · Answer 2

plc check below query...

declare @a int=0

declare @t table(id int)

insert into @t(id) values(1),(2),(3)

select @a=ID from @t

select @a first

/******************STMT*******************/

set @a=0

select @a=@a+ID from @t

select @a second

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 3

Check this out. My team ran into an issue when concatenating like in your example. The issue was that the output was different for the same query when a certain index was present/absent.

http://connect.microsoft.com/SQLServer/feedback/details/383641/in-variable-concatenation-and-order-by-filters-results-like-where-condition

https://sqlroadie.com/

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 4

So it is as simple as the output being redirected from the results pane to the variable. I expected the successful concatenation into the variable but not the redirection as well.

Thank to all for the responses.