Why won't this query return data?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Why won't this query return data?

Why won't this query return data?

Rate Topic

Display Mode

Topic Options

Author

Message

jshahan

Posted Wednesday, October 24, 2012 4:30 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:49 PM
Points: 134, Visits: 716

-- 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 Smile

)
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

Post #1376720

CapnHector

Posted Wednesday, October 24, 2012 5:05 PM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:07 AM
Points: 935, Visits: 1,709

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 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

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

Jeremy Oursler

Post #1376728

subbareddy542

Posted Thursday, October 25, 2012 12:15 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 8:18 PM
Points: 832, Visits: 610

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

Post #1376792

Arjun Sivadasan

Posted Thursday, October 25, 2012 5:50 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 09, 2013 3:29 AM
Points: 365, Visits: 697

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

Post #1376938

jshahan

Posted Thursday, October 25, 2012 7:59 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:49 PM
Points: 134, Visits: 716

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.

Post #1377001

« Prev Topic | Next Topic »

Permissions