Solved

Proc to return email addresses from ID's

Posted on 2013-11-01
6
302 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

831 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