CODEWITHSUNDEEP
sqlsql-servert-sqlreporting-servicessql-server-2014
user6780741
user6780741

Reputation: 51

How do I correctly implement quotename in SQL Server?

Background Info:

I have a stored procedure that is populating an SSRS report. The SSRS report is ran and exported as a CSV. It is then opened as textfile and ran through a 3 party vendor application.

The output of the text book should look like this:

    lid, status, i_flag,Count, pDate, iDate
    62558633,"Text Value","08/16",11,"08/16","08/16"
    78013526,"Text Value","",,"08/16""08/16"

My results look like this: 

    lid, status, i_flag,Count,pDate,iDate
    19007442,"'Dir,MgmtII'",'',2,'','02/16' 
    17343623,'Text','',0,'11/15','02/16'

Now the code that I'm using is: 

SELECT 
    quotename(isnull(i_flag,''''), '''') as i_flag,
    isnull(lid, 0) as lid,
    quotename(isnull(status,''''), '''') as status,
    isnull(Count, 0) as Count,
    quotename(isnull(p_Date,''''), '''') as p_Date,
    quotename(isnull(i_Date,''''), '''') as i_Date
FROM
    #Table

Any ideas on how I can fix this. Been stumped on this for a bit. Thanks.

Upvotes: 2

Views: 2211

Answers (1)

3N1GM4
3N1GM4

Reputation: 3351

If I'm understanding your question correctly (which I'm quite possibly not), I think you want:

SELECT 
    QUOTENAME(ISNULL(i_flag,''), '"') AS i_flag,
    ISNULL(lid, 0) AS lid,
    QUOTENAME(ISNULL([status],''), '"') AS [status],
    ISNULL([Count], 0) AS [Count],
    QUOTENAME(ISNULL(p_Date,''), '"') AS p_Date,
    QUOTENAME(ISNULL(i_Date,''), '"') AS i_Date
FROM
    #Table

It sounds like you have some values in fields which you wish to wrap in double quotes " for the purpose of exporting to CSV, plus in some cases the values in these fields might be NULL.

My suggestion above handles this by first using ISNULL to replace any NULL values with an empty string and then using QUOTENAME to wrap the resultant value in double quotes.

The crucial differences to your posted code are:

  • When using ISNULL I replace the NULL with an empty string '' instead of a string containing a single quote character '''' (two consecutive single quotes within a string represent an escaped literal single quote character)
  • When using QUOTENAME to wrap the values in double quotes, I specify a string containing a double quote '"' in the second parameter, instead of a string containing a single quote character ''''.

I hope that helps you - if you're still having problems, perhaps you could provide some sample rows from your #Table temp table and the output you're expecting from the query so people can help you further.

As an aside, it's not good practice to use SQL reserved keywords like status or count as column names or aliases, but if you must, I'd recommend enclosing them in brackets (i.e. [status]) for readability (especially in SSMS or any IDE with SQL syntac highlighting or colour-coding) as I have done above.

Upvotes: 4

Related Questions