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
LVL 3
Andy GreenAsked:
Who is Participating?
 
Louis01Commented:
I use this function:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_StringToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_StringToTable]
GO

CREATE FUNCTION [dbo].[fn_StringToTable]
  (
   @string VARCHAR(MAX)
 , @delimiter CHAR(1)
  )
RETURNS @output TABLE (data VARCHAR(4000))
  BEGIN
    DECLARE
      @start INT
    , @end INT
    SELECT
      @start = 1
    , @end = CHARINDEX(@delimiter, @string)
    WHILE @start < LEN(@string) + 1 
      BEGIN
        IF @end = 0 
          SET @end = LEN(@string) + 1

        INSERT  INTO @output
                (data)
        VALUES
                (SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
      END
    RETURN
  END
GO

Open in new window

to convert the string to a table as follows:
select *
  from myTable
 where ID in (select [data] from dbo.fn_StringToTable('1,2,3'));

Open in new window

0
 
Andy GreenAuthor Commented:
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
0
 
Louis01Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ephraim WangoyaCommented:
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

0
 
deanvanrooyenCommented:
sometimes its better to use the technology as it is intended, use a table value parameter if your not stuck on SQL 2005

http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

if you find your doing this a lot make the tvp generic, e.g. one int column.

this is better than strings a you should really be validating strings if you're passing them into  procs, or at least paramterized.
0
 
Andy GreenAuthor Commented:
Thanks.

Used Louis01 function but like the TVP from deanvanrooyen - going to that a lot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.