Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2009
    Posts
    18

    Unanswered: How to declare a set?

    I can find simple on-line example for stuffs like:

    DECLARE @myVar AS varchar(8)
    SET @myVar = '00000000'

    But I need something along the line of the code below:

    DECLARE @myList AS varchar(??????)
    SET @myList = ('00000000','00000001','00000002','00000004')

    Please help. Thanks!

  2. #2
    Join Date
    May 2009
    Posts
    13

    Try Splitting the List Into a Table

    A coworker just had that question, and I created a table-valued function to break down his input list.

    With such a function defined, you can easily use it in SQL. For example:

    select * from fn_splitSetIntoVarchars('mary,had,a,little,lamb')
    GO

    mary
    had
    a
    little
    lamb


    Here's a function definition:

    CREATE FUNCTION dbo.fn_splitSetIntoVarchars (@set varchar(8000))
    -- function returns a 1 column table with a row for each comma separated string contained in @set
    RETURNS @outTable TABLE (item varchar(1000))
    AS
    begin

    declare @pointer int
    declare @numChars int
    set @pointer = 1
    while @pointer < LEN(@set)
    begin
    set @numChars = CHARINDEX(',',@set, @pointer + 1) - @pointer
    if @numChars < 1 -- no more commas
    set @numChars = (LEN(@set) - @pointer) + 1
    insert into @outTable values (SUBSTRING(@set, @pointer,@numChars))
    set @pointer = @pointer + @numChars + 1
    end
    return
    end
    GO

  3. #3
    Join Date
    Oct 2009
    Posts
    18
    Thanks, rgauch. Not sure how your function can be applied in my case though.

    What I'd like to do is to turn the following:

    SELECT * FROM myTable WHERE myValue IN ('0000', '0001', '0002', '0003')

    into something like:

    SET @mySet = ('0000', '0001', '0002', '0003')
    SELECT * FROM myTable WHERE myValue IN @@mySet



    The gist of the question here is how to declare
    @mySet
    so that
    SET @mySet = ('0000', '0001', '0002', '0003')
    doesn't crash?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem that you're having is that Transact SQL does not have a "set" datatype, so you can't create a "set" variable. Transact SQL can create a table variable and load the rows within that variable with the values that interest you, which is what rgauch has shown you how to do.

    Like all programming languages, Transact SQL has limits. You have to learn to code within those limits.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well sort of. I think this is kind of like not being able to see the forest for the trees. SQL is all about "SETS", only in databases they are referred to as tables. So, declare a table, temporary table, or a table variable (best choice for you, I think) and load your data into that.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For example:
    Code:
    declare	@MyList table (MyListData varchar(8))
    insert into @MyList (MyListData)
    select	'00000000'
    union select '00000001'
    union select '00000002'
    union select '00000004'
    
    select	*
    from	@MyList
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2009
    Posts
    18
    Thanks all for your help.

    I'll save rgauch's answer for later studying. In the meantime, blindman's table example really slams everything home for me. His "tree" and "forest" verbiage escaped me thought. Ironically, I'm the blind man who couldn't see the tree nor the forest. Cheers!

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by knhcmp View Post
    Thanks all for your help.

    I'll save rgauch's answer for later studying. In the meantime, blindman's table example really slams everything home for me. His "tree" and "forest" verbiage escaped me thought. Ironically, I'm the blind man who couldn't see the tree nor the forest. Cheers!
    No...he can be pretty blind at times...see he missed the ALL in the UNION Statement....hence the name
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Uh, nope. I would not use UNION ALL for that purpose. Simply invites duplicates.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •