Professional Web Applications Themes

How create simple cursor? - Microsoft SQL / MS SQL Server

I need to write a stored proc against a table to return a single string containing a concatenation of all email addresses, separated with semi-colons. ID Email 1 [email]samhotmail.com[/email] 2 [email]davemsn.com[/email] 3 [email]julie[/email] Result = [email]samhotmail.com;davemsn.com;julie[/email] I'm assuming I need to use a cursor. Any quick sample or example would be appreciated. Thanks, Ron Ronald S. Cook, MCSD, MCT Sr. Systems Consultant Westin Information Services, Inc. [url]http://www.westinis.com[/url]...

  1. #1

    Default How create simple cursor?

    I need to write a stored proc against a table to return a single string
    containing a concatenation of all email addresses, separated with
    semi-colons.

    ID Email
    1 [email]samhotmail.com[/email]
    2 [email]davemsn.com[/email]
    3 [email]julie[/email]

    Result = [email]samhotmail.com;davemsn.com;julie[/email]

    I'm assuming I need to use a cursor. Any quick sample or example would be
    appreciated.

    Thanks,
    Ron

    Ronald S. Cook, MCSD, MCT
    Sr. Systems Consultant
    Westin Information Services, Inc.
    [url]http://www.westinis.com[/url]


    Ronald S. Cook Guest

  2. #2

    Default Re: How create simple cursor?

    Ron,
    > I need to write a stored proc against a table to return a single
    > string containing a concatenation of all email addresses,
    > separated with semi-colons.
    >
    > ID Email
    > 1 [email]samhotmail.com[/email]
    > 2 [email]davemsn.com[/email]
    > 3 [email]julie[/email]
    >
    > Result = [email]samhotmail.com;davemsn.com;julie[/email]

    You can do this with a simple loop.

    declare count int
    declare result table (
    ProductName varchar(40),
    List varchar(7995)
    )
    insert result
    select min(ProductName), min(ProductName) from Products
    select count = rowcount

    while (count > 0) begin
    update result set
    ProductName = Products.ProductName,
    List = result.List + ';' + Products.ProductName
    from result result, Products
    where Products.ProductName = (
    select min(ProductName) from Products
    where Products.ProductName > result.ProductName)
    select count = rowcount
    end
    select List from result


    Or you can use bcp and bulk insert to do the concatenation for you.


    create table #File (Body varchar(8000))
    declare List varchar(8000)

    exec master..xp_cmdshell 'bcp "select ProductName from
    Northwind..Products order by ProductName" queryout
    C:\Products.txt -c -t -r; -T -Slindaw\ddbt'

    bulk insert #File from 'C:\Products.txt'
    with (fieldterminator = '', rowterminator = '\0')

    set List = (select top 1 left(Body, len(Body)-1) from #File)
    select List List

    drop table #File


    Both of these methods assume that the output string will not exceed
    8000 characters.
    They will also work if the number of items to concatenate is
    unknown.

    Linda


    lindawie Guest

  3. #3

    Default Re: How create simple cursor?

    Or:

    Thanks to David Portas for the DDL.

    DECLARE string varchar(1000)
    SELECT string = COALESCE(string + '; ' + email, email, '') FROM Contacts
    SELECT string


    Results:

    [email]davemsn.com[/email]; [email]julie[/email]; [email]samhotmail.com[/email]


    "Ronald S. Cook" <rscookwestinis.com> wrote in message
    news:%23N8taY0PDHA.1624tk2msftngp13.phx.gbl...
    > I need to write a stored proc against a table to return a single string
    > containing a concatenation of all email addresses, separated with
    > semi-colons.
    >
    > ID Email
    > 1 [email]samhotmail.com[/email]
    > 2 [email]davemsn.com[/email]
    > 3 [email]julie[/email]
    >
    > Result = [email]samhotmail.com;davemsn.com;julie[/email]
    >
    > I'm assuming I need to use a cursor. Any quick sample or example would be
    > appreciated.
    >
    > Thanks,
    > Ron
    >
    > Ronald S. Cook, MCSD, MCT
    > Sr. Systems Consultant
    > Westin Information Services, Inc.
    > [url]http://www.westinis.com[/url]
    >
    >

    Steve Beach Guest

Similar Threads

  1. create a simple search
    By emmim44 in forum Macromedia ColdFusion
    Replies: 3
    Last Post: March 16th, 11:04 PM
  2. How to create this simple DataGrid?
    By Miguel Dias Moura in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 15th, 11:41 AM
  3. Replies: 28
    Last Post: October 21st, 04:48 PM
  4. How to create simple 3D in Freehand MX
    By august_333 webforumsuser@macromedia.com in forum Macromedia Freehand
    Replies: 0
    Last Post: September 10th, 02:32 AM
  5. How to create a dynamic cursor?
    By Ryan Gaffuri in forum Oracle Server
    Replies: 3
    Last Post: January 3rd, 12:39 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