Solved

Proc to return email addresses from ID's

Posted on 2013-11-01
6
300 Views
Last Modified: 2013-11-06
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
0
Comment
Question by:Andy Green
6 Comments
 
LVL 11

Accepted Solution

by:
Louis01 earned 300 total points
ID: 39616299
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
 
LVL 3

Author Comment

by:Andy Green
ID: 39616827
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
 
LVL 11

Expert Comment

by:Louis01
ID: 39617251
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 32

Expert Comment

by:ewangoya
ID: 39619800
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
 
LVL 12

Assisted Solution

by:deanvanrooyen
deanvanrooyen earned 200 total points
ID: 39620000
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
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 39627877
Thanks.

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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now