Why won't this query return data?

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

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

  • 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

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

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

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

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