asked on

Proc to return email addresses from ID's

I have a checkboxlist bound with id's and names, what I need to do is send an email to everyone selected.

I'm checking for the checked boxes and creating a comma separated list of ID's. I need to convert these ID's into email Addresses from SQL.

In my web app I have a function:

Dim SendToEmails As String = Communication.GetEmailByID("SendToIDs")

This calls a helper function that returns a string of emails, its this I'm having trouble with. I want to convert the stings of ID's into a string of emails that cam be used in the recipient field of the mail object.

Or does anyone have a better way. I can't use the email address as the ID field as not all users will have an email address. (I have to sort this bit out later - I need to know which users didn't get sent am email)

Andy

ASKER CERTIFIED SOLUTION

Louis01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andy Green

ASKER

Thanks, Buts its not the converting the string to a table its to get a string of email addresses bases on the string of id's

Andy

Louis01

Not sure I understand, but let's try this:

Using the same function, and assuming you have a table called MyTable with ID and Email as fields, you can get it by the following sql:

select Email 
  from myTable
 where ID in (select [data] from dbo.fn_StringToTable('1,2,3'));

Open in new window

Getting the ID's in a table structure allows you to use the IN clause (or EXISTS or INNER JOIN) to filter it from your table.

Hope this helps?

Ephraim Wangoya

Use Louse idea to parse the ids but then create a string of the emails as follows

CREATE FUNCTION fnIDListToEmailList(@IDList varchar(1000))
RETURNS varchar(max)
AS
BEGIN
	DECLARE @ResultVar varchar(max)
	DECLARE @start INT = 1
    DECLARE @end INT= CHARINDEX(',', @IDList)
    
	WHILE @start < LEN(@IDList) + 1 
      BEGIN
        IF @end = 0 
          SET @end = LEN(@IDList) + 1

        select @ResultVar =ISNULL(@ResultVar + ',', '') + emailfield 
        from emailstable
        where id = SUBSTRING(@IDList, @start, @end - @start)
        
        SET @start = @end + 1
        SET @end = CHARINDEX(',', @IDList, @start)
      END
    

	-- Return the result of the function
	RETURN @ResultVar

END
GO

Open in new window

For a simple test, you can do

select dbo.fnIDListToEmailList('1,2,3,4')

Open in new window

SOLUTION

deanvanrooyen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andy Green

ASKER

Thanks.

Used Louis01 function but like the TVP from deanvanrooyen - going to that a lot.