September 12, 2013 at 9:57 am
I have a need of passing-in an array of email addresses from a VB6 application to SQL Server 2012 stored procedure, about 20 email addresses total per array, and they'll look basically like this as they come into the SQL Server stored procedure:
"EmailOne@gmail.com;EmailTwo@gmail.com;EmailThree@gmail.com;EmailFour@gmail.com"
... and so on
What I basically would like to do within my SQL Server 2012 stored procedure is parse/split each of the individual email addresses contained in the array and run individual "Update" statements to indicate in a particular table that I've successfully sent an email to each of the approx. 20 email addresses.
I know that I could loop through the array of 20-or-so email addresses within my VB6 application and call the SQL Server stored procedure 20 different times and do the updates to the records containing each email address in the array. However, I was curious if I could just pass all 20 email addresses, separated by the ";" delimiter as I showed in the example earlier, and do everything with *ONE* call to a stored procedure instead of 20 individual calls.
Any suggestions or examples are much appreciated.
Thanks.
September 12, 2013 at 10:02 am
Take a look at the article in my signature about splitting strings. In there you will find a table valued function for doing this.
The code when you are done would be something like this:
declare @Emails varchar(2000) = 'EmailOne@gmail.com;EmailTwo@gmail.com;EmailThree@gmail.com;EmailFour@gmail.com'
select *
from dbo.DelimitedSplit8K(@Emails, ';')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2013 at 10:17 am
Many thanks for the info. I'll check this out and will try to implement / modify it as needed this eve.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply