Friday, January 9, 2009

Retrive CSV from DATABASE

HI
Aprox in every project we need to send mail to all registred user then we can retrive emailAddress
in CSV format and form frontend we can fetch all values from for loop and send these emails to user
in single database cycle.

Basic example of this type SP is as follows :-


create proc SelectEmailAddressInCSV
as
set nocount on
DECLARE @xyz VARCHAR(100)
declare @abc varchar(100)

set @abc=''

declare selemail cursor for

select email from dataadi

open selemail
fetch NEXT from selemail into @xyz
WHILE @@Fetch_status = 0
begin


set @abc= @abc+ @xyz+','

FETCH NEXT FROM selemail INTO @xyz
END
print @abc
CLOSE selemail
DEALLOCATE selemail


Table of dataadi is as follows

CREATE TABLE [dbo].[dataadi](
[ID] [int] NOT NULL,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_dataadi] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


So Run this script and modified as your need


simppily we can add these three lines

DECLARE @values varchar(150), @delimiter char
SET @delimiter = ','
SELECT @values = COALESCE(@values + @delimiter, '') + u_name FROM tblsplit
SELECT @values AS [List of Emails]

Thanks
HelpOnDesk Team

1 comment:

Mahesh K. Sharma said...

DECLARE @values varchar(150), @delimiter char
SET @delimiter = ','
SELECT @values = COALESCE(@values + @delimiter, '') + u_name FROM tblsplit
SELECT @values AS [List of Emails]