Select issue

**selanec** · Feb 26th, 2009, 06:23 PM

I need to send batch emails. Well some groups (on certain criteria) contain e.g. 600 emails while some contain just 90 but the limit of the BCC field is 100 (ISP limitation).

How do i send all the email groups splitting those of 600 to 6 equal groups?

The emails are stored to the SQL Server 2000 database.

Thanks

**sr_jay** · Feb 27th, 2009, 02:49 AM

Hi,

Can you determine beforehand the total number of emails in each criteria? If you can and if it is greater than 100 for a certain criteria then you can use a while loop or something and write code in that loop and send the emails in batches. That is if there are 600 addresses then you can write some code in a while loop and that sends 100 emails in each pass of the loop. I agree it is tedious coding but I am not sure if you can change the limitation of 100 in any way.

Regards,
sr_jay

Originally Posted by selanec

I need to send batch emails. Well some groups (on certain criteria) contain e.g. 600 emails while some contain just 90 but the limit of the BCC field is 100 (ISP limitation).

How do i send all the email groups splitting those of 600 to 6 equal groups?

The emails are stored to the SQL Server 2000 database.

Thanks

**selanec** · Feb 27th, 2009, 03:25 AM

Hi sr_jay, thanks for the pointers. it's much appreciated. Can you at least post a small code snippet that i better understand the idea. thanks

**MrNorth** · Feb 27th, 2009, 04:05 AM

Hi!

I did an app that would send heaps of emails (several thousands), and I used a database table where I stored the emails to be sent and then had a stored procedure loop through each row and send the mail, when sent was complete I deleted the row.

/Henrik

**MrNorth** · Feb 27th, 2009, 04:06 AM

Here is a link that can point you in the right way:

http://classicasp.aspfaq.com/email/h...ql-server.html

**sr_jay** · Feb 27th, 2009, 05:38 AM

Here is some sample code I threw together in a hurry. In this code first a data table with two columns called "Email" and "Category" is created. And 539 records are added to this data table. The first 200 records have their "Category" column set to "Cat1". The remaining 339 records have their Category column set to "Cat2". Now the Email field of the first record has a value of "[email protected]" and in the second record has a value of "[email protected]" and so on and so forth till the last record which has "[email protected]" in the Email field.

Then there is code that sends the emails. 100 records are sent in one email. In the first email that is sent the "to" address is set to the Email address in the Email field of the 0th record and the "Bcc" field set to all the email id's in the Email fields of the records from 1 to 99. In the second email that is sent the "to" address is set to the Email field of the 100th record and the "Bcc" field set to records from 101 to 199 and so on and so forth till all the records are sent.

Finally once you fully execute the code all you will see is the web page with a text box telling you that the number of mail message objects created was 6 and the total number of mails sent was also 6. But everyone would have received the emails.

Open a web application in VS 2005. It will create the Default page. Just substitute the code I give below for both Default.aspx and Default.aspx.cs and run the page.

HTML Code for Default.aspx

Code:

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="TextBox1" TextMode="MultiLine" runat="server" Style="z-index: 100; left: 175px; position: absolute;
            top: 64px" Height="82px" Width="356px"></asp:TextBox>
    
    </div>
    </form>
</body>
</html>

Code for Default.aspx.cs

Code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Mail;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dt = new DataTable("EmailTable");
        dt.Columns.Add("Email", System.Type.GetType("System.String"));
        dt.Columns.Add("Category", System.Type.GetType("System.String"));

        string domain = "@mydomain.com";
        for(int i= 0; i < 539; i++)
        {
            DataRow dr = dt.NewRow();
            dt.Rows.Add(dr);
            if (i < 200)
            {
                dr["Email"] = "sample" + i.ToString() + domain;
                dr["Category"] = "Cat1";
            }
            else
                {
                    dr["Email"] = "sample" + i.ToString() + domain;
                    dr["Category"] = "Cat2";
                }
        }
        string cat = dt.Rows[0]["Category"].ToString();
        string q = "";
        int pt = 0;
        int ncreated = 0;
        int nsent = 0;
        bool sentlast = false;
        string bccval = "";
        MailMessage msg = null;
        while(pt < dt.Rows.Count)
        {
            if (cat != dt.Rows[pt]["Category"].ToString())            
                cat = dt.Rows[pt]["Category"].ToString();
            DataRow[] drlist = dt.Select("Category='" + cat + "'");
            int numemails = drlist.Length;
            int j = 0;
            while (j < numemails)
            {
                  if((j % 100) == 0)
                  {                      
                      if (msg != null)
                      {
                          sentlast = true;
                          msg.From = "[email protected]";
                          msg.Bcc = bccval;
                          SmtpMail.Send(msg);
                          nsent++;
                          bccval = "";
                      }
                      msg = new MailMessage();
                      msg.Body = "This is fine";
                      ncreated++;
                  }
                  if (j % 100 == 0)
                      msg.To = drlist[j]["Email"].ToString();
                  else
                      bccval += drlist[j]["Email"].ToString() + ",";
                  j++;      
                  pt++;
                  sentlast = false;
            }
        }
        if (!sentlast)
        {
            sentlast = true;
            msg.From = "[email protected]";
            msg.Bcc = bccval;
            SmtpMail.Send(msg);
            nsent++;
        }
        q += "Number of mail message objects created = " + ncreated.ToString() + Environment.NewLine;
        q += "Number of mail message objects sent = " + nsent.ToString() + Environment.NewLine;
        TextBox1.Text = q;
    }
}

It is a very complicated piece of coding and it works. All email id's are dummies. So do not expect anyone to receive emails. You can just go ahead and try out the code. You may have to analyze the code and adapt it to suit your specific purpose

.

Hope this helps.

Warm Regards,
sr_jay

Originally Posted by selanec

Hi sr_jay, thanks for the pointers. it's much appreciated. Can you at least post a small code snippet that i better understand the idea. thanks

**selanec** · Feb 27th, 2009, 06:48 AM

Thank you sr_jay, i will test your code shortly and let you know if ti suits my needs. Actaully i am more interested in sending emails from the sql server directly. MrNorth's idea sounds very good.

Originally Posted by MrNorth

Hi!

I did an app that would send heaps of emails (several thousands), and I used a database table where I stored the emails to be sent and then had a stored procedure loop through each row and send the mail, when sent was complete I deleted the row.

/Henrik

I CHECKED the link you posted but it is too general ... so i was wondering if you can post some of your code here. or pm me with the code.
However notice that my main issue is that all of these emails are different.
That's why i want to group them somehow that i don't send 1000's diferent emails as all emails from one group should receive the same email.\\

Thanks

**Pradeep1210** · Feb 27th, 2009, 07:40 AM

See if this helps:

vb.net Code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim emailGroup As String
    'I assume you have queried your database and got the emails in this variable.
    emailGroup = "[email protected]; [email][email protected][/email]; [email][email protected][/email]; [email][email protected];[email protected][/email]; [email][email protected][/email]; " & _
                 "[email protected];[email protected]; [email][email protected][/email]; [email][email protected];[email protected][/email]; [email][email protected][/email]; [email][email protected][/email]"
 
    'we get the batches 
    Dim emailBatches As List(Of String)
    emailBatches = GetEmailBatches(emailGroup, 100, ";")
 
    'now we have all the batches (100 chars. max per batch) in our emailBatches variable
    'so we can enumerate thru them and send emails
    For Each batch As String In emailBatches
        'Debug.Print(batch)
        MsgBox(batch)
    Next
End Sub
 
Private Function GetEmailBatches(ByVal emailGroup As String, ByVal maxLengthPerBatch As Integer, ByVal emailSeperator As String) As List(Of String)
    Dim emailBatches As New List(Of String)
    Dim idx As Integer
 
    If Not emailGroup.EndsWith(emailSeperator) Then emailGroup &= emailSeperator
    Do While emailGroup.Length > 0
        idx = emailGroup.LastIndexOf(emailSeperator, Math.Min(emailGroup.Length, maxLengthPerBatch))
        emailBatches.Add(emailGroup.Substring(0, idx + 1))
        emailGroup = emailGroup.Substring(idx + 1).Trim
    Loop
    Return emailBatches
End Function

**selanec** · Feb 27th, 2009, 09:45 AM

Looks good but, as soon as i set the maxLength to 10 i get an System.OutOfMemoryException exception, on this line:
emailBatches.Add(emailGroup.Substring(0, idx + 1))

Ok let me explain what i need actually.

say we have these email addresses:

emailGroup = "[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; " & _
"[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]" & _
"[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]"

Means now we have:
11 - 123.com
5 - 789.com
2 - 456.com
2 - 101.com

And say we set the maxLengthPerBatch to 2.
The result should be 6 batches for 123.com, 3 batches for 789.com and one batch for 456.com and 101.com.

Means if i iterate emailBatches generics list

Code:

For Each batch As String In emailBatches
   Response.Write(batch & "<br />")
Next

I should get:

[email protected]; [email protected];
[email protected]; [email protected];
[email protected]; [email protected];
[email protected]; [email protected];
[email protected]; [email protected];
[email protected];
[email protected]; [email protected];
[email protected]; [email protected];
[email protected];
etc.

Thank you very much

**Pradeep1210** · Feb 27th, 2009, 10:15 AM

Originally Posted by selanec

Looks good but, as soon as i set the maxLength to 10 i get an System.OutOfMemoryException exception, on this line:
emailBatches.Add(emailGroup.Substring(0, idx + 1))

Ohhhh... I seem to have misinterpreted your problem.

... but the limit of the BCC field is 100 (ISP limitation).

Is this 100 email addresses or 100 characters in the BCC field?
means is the restriction provided by your ISP xx characters or xx email addresses per message?

That procedure was designed to split it on xx characters.
i.e. each batch has maxLengthPerBatch characters irrespective of the number of email addresses in it.

Also you must ensure that maxLengthPerBatch is bigger than the length of longest email address to avoid that System.OutOfMemoryException exception.
e.g. you are splitting on 10 characters, but [email protected] contains 15. So maxLengthPerBatch must be bigger than 15.

**selanec** · Feb 27th, 2009, 10:25 AM

Is this 100 email addresses or 100 characters in the BCC field?

It is 100 email addresses. Can you please help a bit further? Thanks

**selanec** · Feb 27th, 2009, 10:55 AM

this is a rough pseudo code of what i need:

SELECT email, url FROM temptable GROUP BY url

' set the maximum allowed emails for a single group e.g. 100 (the most confusing group).
' then just iterate the groups:

For Each Group As List(Of String) In Groups
For Each email As String In Group
SendEmail(email)
Next
Next

Seems simple actually but, it is not. Thanks

**Pradeep1210** · Feb 27th, 2009, 11:56 AM

Originally Posted by selanec

It is 100 email addresses. Can you please help a bit further? Thanks

ok.. here's the modified procedure.

Now maxLengthPerBatch is Number of email addresses in each batch, instead of number of characters.

vb.net Code:

Private Function GetEmailBatches(ByVal emailGroup As String, ByVal maxLengthPerBatch As Integer, ByVal emailSeperator As String) As List(Of String)
    Dim emailBatches As New List(Of String)
    Dim emails(), thisBatch() As String
    Dim emailCount As Integer
 
    emails = emailGroup.Split(Split(emailSeperator), StringSplitOptions.RemoveEmptyEntries)
    For i As Integer = 0 To emails.Length Step maxLengthPerBatch
        emailCount = Math.Min(emails.Count - i, maxLengthPerBatch)
        ReDim thisBatch(emailCount)
        Array.ConstrainedCopy(emails, i, thisBatch, 0, emailCount)
        emailBatches.Add(Join(thisBatch, emailSeperator))
    Next
    Return emailBatches
End Function

**selanec** · Feb 27th, 2009, 12:08 PM

hmm looks very close but it yet doesn't return what i need.

e.g. if i set the maxLengthPerBatch to 2 it will return them in groups but without to be grouped properly.
Maybe i should sort the list before the function returns the generic list?

But i don't know how to set the comparer to sorts the items per domain.

@123.com ... maybe i can use split function and do something after?
Any ideas? this is really close .. well i hoped that i will make this on the database layer but anyway i am fine with this approach. thanks in advance

**Pradeep1210** · Feb 27th, 2009, 12:21 PM

Are you using VS2005 or VS2008?

**selanec** · Feb 27th, 2009, 12:34 PM

I am using both. i mean i have installed FW 3.5, and have VS 2k5 on this and VS 2k8 on another machine. Thanks

**Pradeep1210** · Feb 28th, 2009, 03:06 AM

Here it is, using LINQ:

vb.net Code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim emailGroup As String
    emailGroup = "[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; " & _
                 "[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; " & _
                 "[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]"
 
    'set the max email addresses per batch, and email seperator character
    Dim maxLengthPerBatch As Integer = 4
    Dim emailSeperator As String = ";"
 
    counter(True, maxLengthPerBatch)
    Dim emailBatches = From email In emailGroup.Split(emailSeperator.ToCharArray) _
                       Group email By url = email.Split("@"c)(1) Into email = Distinct() _
                       Select splitted = From em In email _
                            Group em By gnum = (counter() Mod email.Count) \ maxLengthPerBatch Into em = Group _
                            Order By gnum, em _
                            Select emails = Join(em.ToArray, emailSeperator)
    emailBatches = emailBatches.ToList
 
    'now we have all the batches in our emailBatches variable
    'so we can enumerate thru them and send emails
 
    'let's see if it prints correctly in our debug window.
    For Each batch In emailBatches
        For Each item As String In batch
            Debug.WriteLine(item)
        Next
    Next
End Sub
 
'dummy procedure to mimic i++ kind of thing in C#, since vb.net doesn't have one
Private Function counter(Optional ByVal reset As Boolean = False, Optional ByVal resetSeed As Integer = -1) As Integer
    Static i As Integer = resetSeed
    If reset Then i = resetSeed
    i += 1
    Return i
End Function

Pradeep

**Pradeep1210** · Feb 28th, 2009, 02:30 PM

ok... here's a better implementation. It doesn't need a static variable.

vb Code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim emailGroup As String
    emailGroup = "[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; " & _
                 "[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; " & _
                 "[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]"
 
    'set the max email addresses per batch, and email seperator character
    Dim maxLengthPerBatch As Integer = 4
    Dim emailSeperator As String = ";"
 
    Dim rownum As Integer = 0
    Dim emailBatches = From email In emailGroup.Split(emailSeperator.ToCharArray) _
                       Group email By url = email.Split("@"c)(1) Into email = Distinct() _
                       Select splitted = From em In email _
                            Group em By gnum = (inc(rownum) Mod email.Count) \ maxLengthPerBatch Into em = Group _
                            Select emails = Join(em.ToArray, emailSeperator)
 
    'now we have all the batches in our emailBatches variable
    'so we can enumerate thru them and send emails
 
    'let's see if it prints correctly in our debug window.
    For Each batch In emailBatches
        For Each item In batch
            Debug.WriteLine(item)
        Next
    Next
End Sub
 
'workaround to get the effect of C# i++ thing
Private Function inc(ByRef i As Integer) As Integer
    i += 1
    Return i
End Function

**selanec** · Mar 2nd, 2009, 04:57 AM

Omg this post has gone in a totally different direction to what I originally planned to ask. Please let me explain it again.

i have a table. one column is email addresses. the other is url's

the email addresses columns is about 5.000 rows, of course all with unique email addresses

the url column has maybe 800 unique urls, so many rows will be the same

Now what i need is to create sort of temp table within the SP with also 2 fields.... url and a field named bcc.

The BCC field will contain all the email addresses from the other table that have the same url

So for example: if url http://www.something.com has 300 rows or email addresses, in the new table it would be one row with a field that has the 300 email addresses in them comma delimited.

After that i only want a max of 100 emails per record. so if http://www.something.com has 300 emails associated with it, the end table would only have 3 records for http://www.something.com. This is a ISP limitation for the BCC field. odd i know

My goal is to improve this process so that instead of sending 5000 individual emails i send 800 using BCC field.

Thank you and sorry for the confusion

**Pradeep1210** · Mar 2nd, 2009, 07:11 AM

Did you try my last code example. It seems to be doing exactly what you need.
If you already have all email addresses in a database column, you need not necessarily make it a comma seperaed string. Instead you can just modify only one line and use that database column/list/arraylist or collection etc.

Just replace:

Code:

Dim emailBatches = From email In emailGroup.Split(emailSeperator.ToCharArray) _

with

Code:

Dim emailBatches = From email In emailGroup _

and it should start working with your list. (assuming it is named emailGroup)

**sr_jay** · Mar 3rd, 2009, 01:11 AM

Hi,

I have got a solution here for you that in principle does what you want. To demonstrate it I have created a script file. I am giving the code below.

Code:

CREATE TABLE [emailtable] (
	[email] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[url] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

Insert into emailtable(email, url) Values('a', 'a1');
Insert into emailtable(email, url) Values('b', 'a1');
Insert into emailtable(email, url) Values('c', 'a1');
Insert into emailtable(email, url) Values('d', 'a1');
Insert into emailtable(email, url) Values('e', 'a1');
Insert into emailtable(email, url) Values('f', 'a1');
Insert into emailtable(email, url) Values('g', 'a2');
Insert into emailtable(email, url) Values('h', 'a3');
Insert into emailtable(email, url) Values('i', 'a4');
Insert into emailtable(email, url) Values('j', 'a1');
Insert into emailtable(email, url) Values('k', 'a2');
Insert into emailtable(email, url) Values('l', 'a3');
Insert into emailtable(email, url) Values('m', 'a2');
Insert into emailtable(email, url) Values('n', 'a2');
Insert into emailtable(email, url) Values('o', 'a3');
Insert into emailtable(email, url) Values('p', 'a4');
Insert into emailtable(email, url) Values('q', 'a4');
Insert into emailtable(email, url) Values('r', 'a5');
Insert into emailtable(email, url) Values('s', 'a4');
Insert into emailtable(email, url) Values('t', 'a3');
Insert into emailtable(email, url) Values('u', 'a5');
Insert into emailtable(email, url) Values('v', 'a3');
Insert into emailtable(email, url) Values('w', 'a2');
Insert into emailtable(email, url) Values('x', 'a1');
Insert into emailtable(email, url) Values('y', 'a1');
Insert into emailtable(email, url) Values('z', 'a2');
Go

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [segregate]
(
  @cnt int
)
AS 
select * into #temp from emailtable order by url
Declare mycursor Cursor for
Select * from #temp
Declare @url Varchar(500)
Declare @bcc Varchar(500)
Declare @email Varchar(500)
Declare @tempval varchar(500)
Declare @bcc1 Varchar(5000)
Declare @cnt1 int
Set @cnt1 = 0
Set @tempval = ''
CREATE TABLE #temp1 (
   bcc varchar(5000) NULL,
   url varchar(5000) NULL
);
open mycursor
Fetch NEXT FROM mycursor INTO @email, @url
Set @tempval = ''
While (@@FETCH_STATUS <> -1)
Begin
if(@url <> @tempval)
Begin
  if(@tempval <> '') 
  begin
   Set @bcc1 = SUBSTRING(@bcc, 0, Len(@bcc))
   insert into #temp1(bcc, url) Values (@bcc1, @tempval)   
  end
   Set @tempval = @url
   Set @bcc = ''
   Set @cnt1 = 0
End
if(@cnt1 >= @cnt)
Begin
   Set @bcc1 = SUBSTRING(@bcc, 0, Len(@bcc))
   insert into #temp1(bcc, url) Values (@bcc1, @tempval)   
   Set @bcc = ''
   Set @cnt1 = 0
End
Set @bcc = @bcc + @email + ","
Set @cnt1 = @cnt1 + 1
Fetch NEXT FROM mycursor INTO @email, @url
END
Set @bcc1 = SUBSTRING(@bcc, 0, Len(@bcc))
insert into #temp1(bcc, url) Values (@bcc1, @tempval)   
CLOSE mycursor
DEALLOCATE mycursor
Select * from #temp1
GO

I can tell you what this script does. It first creates a table called emailtable with 2 fields email and url of varchar(500). It uses a create table statement for that. Next it inserts records into this table. It inserts 26 records into this table with distinct values for the email field starting from the first letter of the alphabet 'a' to the last letter of the alphabet 'z'. In the url it inserts values like 'a1', 'a2', 'a3', 'a4', and 'a5'. These are the only allowed values.

Some of the records have the same value for the url field. But the email field is distinct in each record. That is for example the record with email field 'a' and the record with email field 'b' both could the same value 'a1' for the record. Then I have created a stored procedure called segregate.

This procedure takes a parameter called @cnt. It denotes the number of records you want together. If you give it a value of 2 it will pick 2 records with the same url value and create a comma delimited string of the email field. If you specify 3 it will use 3 records and so on and so forth. So if you run the script in sql sever and then execute the statement

Exec segregate 2

it will return you a table with a field called bcc and a field called url. The bcc field will have the email field values delimited strung up together but delimited by commas and since the parameter passed is 2 then it would have a maximum of 2 email field values delimited by a comma in each field and the url field will have the same url.

To demonstrate how the stored procedure works let us say your records in the emailtable are

'a','a1'
'b','a1'
'c','a1'
'd','a2'
'e','a3'
'f','a4'
'g','a4'
'h','a5'
'i','a5'
'j','a5'
'k','a5',
'l','a6'

Then

Exec segregate 2

would give you

'a,b' , 'a1'
'c' , 'a1'
'd' , 'a2'
'e' , 'a3'
'f,g' , 'a4'
'h,i' , 'a5'
'j,k' , 'a5'
'l' , 'a6'

I think this is what you wanted and it can be adapted to your requirements.

Hope this helps.

Warm Regards,
sr_jay

Originally Posted by selanec

Omg this post has gone in a totally different direction to what I originally planned to ask. Please let me explain it again.

i have a table. one column is email addresses. the other is url's

the email addresses columns is about 5.000 rows, of course all with unique email addresses

the url column has maybe 800 unique urls, so many rows will be the same

Now what i need is to create sort of temp table within the SP with also 2 fields.... url and a field named bcc.

The BCC field will contain all the email addresses from the other table that have the same url

So for example: if url http://www.something.com has 300 rows or email addresses, in the new table it would be one row with a field that has the 300 email addresses in them comma delimited.

After that i only want a max of 100 emails per record. so if http://www.something.com has 300 emails associated with it, the end table would only have 3 records for http://www.something.com. This is a ISP limitation for the BCC field. odd i know

My goal is to improve this process so that instead of sending 5000 individual emails i send 800 using BCC field.

Thank you and sorry for the confusion

**Pradeep1210** · Mar 3rd, 2009, 04:58 AM

ok here's a better implementation that doesn't need temp tables or cursors or looping thru the records:

Code:

create procedure GetEmailBatches(@MaxEmailsPerUrl as int, @EmailSeperator as char(2) = ',')
as
begin
	;with mycte(email,url,rownum, gcount) as 
	(select e1.email, e1.url, ROW_NUMBER() over(order by e1.url) as rownum, e2.gcount as gcount from emailtable e1
	 inner join (select url, count(url) as gcount from emailtable group by url) e2 on e1.url = e2.url
	)
	select mycte.url, mycte.gcount,(mycte.rownum % mycte.gcount)/@MaxEmailsPerUrl as groupNumber,
	(SELECT email + @EmailSeperator   FROM mycte e3 
		where e3.url = mycte.url and (e3.rownum % e3.gcount)/@MaxEmailsPerUrl = (mycte.rownum % mycte.gcount)/@MaxEmailsPerUrl
		FOR XML PATH('')) as emails  
	from mycte
	group by mycte.url, mycte.gcount, (mycte.rownum % mycte.gcount)/@MaxEmailsPerUrl 
	order by mycte.url, groupNumber
end

Usage:

Code:

--To get 10 emails per batch seperated by comma (default)
exec GetEmailBatches 10

--To get 10 emails per batch seperated by semicolon
exec GetEmailBatches 10, ';'

EDIT: Since you are looking for a database solution, maybe you would get better response in case you post your question in the database fourm. This forum is specifically for questions related to ASP.NET. Just PM any moderator to move your thread to database forums.

Pradeep

**selanec** · Mar 3rd, 2009, 05:12 AM

Originally Posted by sr_jay

To demonstrate how the stored procedure works let us say your records in the emailtable are

'a','a1'
'b','a1'
'c','a1'
'd','a2'
'e','a3'
'f','a4'
'g','a4'
'h','a5'
'i','a5'
'j','a5'
'k','a5',
'l','a6'

Then

Exec segregate 2

would give you

'a,b' , 'a1'
'c' , 'a1'
'd' , 'a2'
'e' , 'a3'
'f,g' , 'a4'
'h,i' , 'a5'
'j,k' , 'a5'
'l' , 'a6'

This is exactly what i want. i will test it a bit more and let you know if i need some further help. Thanks a lot

Pradeep1210, i will check your code shortly. However you both guys are brilliant. thank you so much !!

Thread: Select issue

Thread Tools

Display

Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Re: Select issue

Posting Permissions