Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why won't this query return data? Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 4:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 184, Visits: 1,076

-- 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
Post #1376720
Posted Wednesday, October 24, 2012 5:05 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:16 AM
Points: 945, Visits: 1,769
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
Posted Thursday, October 25, 2012 12:15 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 5:30 AM
Points: 880, Visits: 669
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
Posted Thursday, October 25, 2012 5:50 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:39 AM
Points: 406, Visits: 772
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
Posted Thursday, October 25, 2012 7:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 184, Visits: 1,076
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 »

Add to briefcase

Permissions Expand / Collapse