Professional Web Applications Themes

Select using a string array - Microsoft SQL / MS SQL Server

IS there a way for me to have something that if I pass a comma-delimetered string (for example 1234,5678,12,67,23,90,19) then I can seperate the values between the commas and retrieve information based on each of those numbers. For example, if I have at present... Create Procedure Test t AS INT SELECT Name,DOB,Phone FROM Names WHERE id=t And I have 100 people to look for, I need to run the procedure 100 times. It would be great if I just entered the comma seperated string and off it went. Any ideas? TIA...

  1. #1

    Default Select using a string array

    IS there a way for me to have something that if I pass a comma-delimetered
    string (for example 1234,5678,12,67,23,90,19) then I can seperate the values
    between the commas and retrieve information based on each of those numbers.

    For example, if I have at present...

    Create Procedure Test

    t AS INT

    SELECT Name,DOB,Phone
    FROM Names
    WHERE id=t

    And I have 100 people to look for, I need to run the procedure 100 times.
    It would be great if I just entered the comma seperated string and off it
    went.

    Any ideas?

    TIA



    J Guest

  2. #2

    Default Re: Select using a string array

    Erland Sommarskog has some great writing on using comma-delimited strings:
    http://www.algonet.se/~sommar/
    See "Arrays and Lists in SQL Server".

    "J" <nospam> wrote in message news:phx.gbl... 
    values 
    numbers. 


    raydan Guest

  3. #3

    Default Select using a string array

    declare a varchar(50),
    sql varchar(8000)

    set a='1,2,3,4,5,6'

    SET sql='select * from mytable where myid in ( ' + a
    + ')'
    EXEC (Sql)

     
    comma-delimetered 
    seperate the values 
    of those numbers. 
    procedure 100 times. 
    string and off it 
    mike Guest

  4. #4

    Default Re: Select using a string array

    [posted and mailed, please reply in news]

    mike (com) writes: 

    While this method works, it has issue with both performance and security.
    See my web article http://www.algonet.se/~sommar/arrays-in-sql.html for
    a more detailed discussion.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  5. #5

    Default Re: Select using a string array

    I don´t understand the problem
    Is it the values into string conditions´ where?

    something as well as

    Declare t varchar(254)
    Select t = '1234,5678,12,67,23,90,19'

    Select Id,Name,DOB,Phone
    From Names
    Where CharIndex(convert(varchar(6),Id),t) > 0
    I hope this help you
    :)

    "J" <nospam> escribió en el mensaje
    news:phx.gbl... 
    values 
    numbers. 


    Ele Guest

Similar Threads

  1. Select From Array in DB
    By jkdoyle in forum Dreamweaver AppDev
    Replies: 5
    Last Post: June 20th, 08:20 PM
  2. Replies: 2
    Last Post: August 16th, 10:26 AM
  3. string constants in select
    By ramesh.vasudevan@verizon.com in forum Informix
    Replies: 3
    Last Post: August 8th, 02:35 PM
  4. Help a newbie! How do I SELECT using a string as a WHERE clause?
    By Pablo Contreras in forum ASP Database
    Replies: 1
    Last Post: July 21st, 05:41 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139