Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Proc to return email addresses from ID's

Posted on 2013-11-01
6
Medium Priority
?
311 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 1200 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 32

Expert Comment

by:Ephraim Wangoya
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 800 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

877 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