SQL Server Performance Forum – Threads Archive

Output in CSV format from sp

What is the best way in stored procedure to fetch output rows into CSV format? We are having almost all the major data types in the results plus we are using the 2 functions inside that SP.
This stored procedure will return more than 35 columns in each output row.
How to handle null values while fetching the result?
Surendra Kalekar

---

Is this? Case when col is null then … else…end Can you post the code you used? Madhivanan Failing to plan is Planning to fail

---

SP will be like this… This sp will add few more columns and that sp will return result in CSV format… few of the columns like address1, address2 etc. having null values. My main concern is, How to return around 10000 rows with around 35 columns in CSV format. I want fastest and more reliable solution.
————————————————————–
Create PROCEDURE ui_export
@exportid int
AS
declare @temp1 int
declare @SQLQuery1 varchar(8000) SET NOCOUNT ON
select @temp1 = companyextradefid FROM companyextradef with (NOLOCK) where sourcedefdesc=’numberofbeds’ SELECT @SQLQuery1 = ‘
SELECT c.companyid, c.companyname, c.address1, c.address2, c.city, c.generalemail, c.bizdescription, c.pobox, cc.firstname, cc.lastname,
(select companyextravalue from companyextra with (NOLOCK) where companyextra.companyid=c.companyid and companyextradefid=’ + str(@temp1) + ‘) numberofbeds,
(select codelabel from Codes WITH (NOLOCK) where codename=”BUSINESS” and codevalue=c.businesscode) as business,
dbo.Get_SIC_NAICS (c.companyid, ”S”) as SICCode,
dbo.Get_SIC_NAICS (c.companyid, ”N”) as NAICSCode,
ticker, yearfounded
From exporthistory e with (nolock), company c with (nolock), companycontact cc with (NOLOCK)
where e.exportid = ‘ + str(@exportid) + ‘ and cast(e.entityid as int) = c.companyid and c.companyid = cc.companyid
and cc.companycontactid =(select top 1 companycontactid from companycontact cc2 with (nolock) where cc2.companyid = c.companyid order by cc.title)
order by e.exporthistoryid ‘ –Print @SQLQuery1
Exec(@SQLQuery1)
SET NOCOUNT OFF
Surendra Kalekar

---

Not sure until you give some sample and expected data
Cant you use this? Select Isnull(Address1,”),Isnull(Address2,”),……. Madhivanan Failing to plan is Planning to fail

---

This is the way I done it but it is taking more time than the normal one.
——————–
SELECT @SQLQuery1 = ‘Select ltrim(str(c.companyid)) +”~ ”+
(CASE WHEN (c.companyname IS NULL) THEN ”” ELSE rtrim(c.companyname) END) +”~ ”+
(CASE WHEN (c.address1 IS NULL) THEN ”” ELSE rtrim(c.address1) END) +”~ ”+
(CASE WHEN (c.address2 IS NULL) THEN ”” ELSE c.address2 END) +”~ ”+
(CASE WHEN (c.city IS NULL) THEN ”” ELSE rtrim(c.city) END) +”~ ”+
(CASE WHEN (c.generalemail IS NULL) THEN ”” ELSE rtrim(c.generalemail) END) +”~ ”+
(CASE WHEN (Convert(varchar(8000),c.bizdescription) IS NULL) THEN ”” ELSE Convert(varchar(8000),c.bizdescription) END) +”~ ”+
(CASE WHEN (c.pobox IS NULL) THEN ”” ELSE c.pobox END) +”~ ”+
(CASE WHEN (cc.firstname IS NULL) THEN ”” ELSE rtrim(cc.firstname) END) +”~ ”+
(CASE WHEN (cc.lastname IS NULL) THEN ”” ELSE rtrim(cc.lastname) END) + ”~ ” +
(CASE WHEN (( select companyextravalue from companyextra with (NOLOCK) where companyextra.companyid=c.companyid and companyextradefid=’ + str(@temp1) + ‘) IS NULL) THEN ””
ELSE (select companyextravalue from companyextra with (NOLOCK) where companyextra.companyid=c.companyid and companyextradefid=’ + str(@temp1) + ‘) END) +”~ ”+
dbo.Get_SIC_NAICS (c.companyid, ”S”) +”~ ”+
dbo.Get_SIC_NAICS (c.companyid, ”N”) +”~ ”+
(CASE WHEN (( select c2.companyname from company c2 with (NOLOCK) where c2.companyid= c.parentcompanyid and c2.ACTIVEYN=”Y”) IS NULL) THEN ””
ELSE (select c2.companyname from company c2 with (NOLOCK) where c2.companyid= c.parentcompanyid and c2.ACTIVEYN=”Y”)END) +”~ ”+
(CASE WHEN (c.ticker IS NULL) THEN ”” ELSE rtrim(c.ticker) END) +”~ ”+
(CASE WHEN (ltrim(str(c.yearfounded)) IS NULL) THEN ”” ELSE ltrim(str(c.yearfounded)) END)
From exporthistory e with (nolock), company c with (nolock), companycontact cc with (NOLOCK)
where e.exportid = ‘ + str(@exportid) + ‘ and cast(e.entityid as int) = c.companyid and c.companyid = cc.companyid
and cc.companycontactid =(select top 1 companycontactid from companycontact cc2 with (nolock) where cc2.companyid = c.companyid order by cc.title)’ Surendra Kalekar

---

If there are null values, do you get empty rows? Madhivanan Failing to plan is Planning to fail

---

There will not be a single row which will be empty. CompanyID, CompanyName and few more field values will not be empty or null. If any field is contin Null values then it will return empty space.
Surendra Kalekar

---

Then the new query you used is the only way although it is slow [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

---

]]>