joshmax joshmax - 3 months ago 8
SQL Question

What am I missing? SQL Conversion Failure

I'm trying to add

stf.PrimarySchoolID
(an integer) into this existing query. I have a bat file that will use this SQL statement to export a CSV. However, when I open the exported CSV I receive this error:

Msg 245, Level 16, State 1, Server [SERVER NAME], Line 13
Conversion failed when converting the varchar value ',"' to data type int.


Note:
stf.StaffNumber
is not an integer, it is alpha-numeric.


SET NOCOUNT ON;

SELECT '"'+
stf.StaffNumber +'","'+
stf.LastName +'","'+
stf.FirstName +'","' +
COALESCE(u.username, '')+'","'+
COALESCE(sfc.[NAME],'') +'",'+
stf.PrimarySchoolID
FROM dbo.UDF_GetCurrentAcademicSessions() cur
JOIN dbo.Course c ON c.AcadSessionID = cur.AcadSessionId
JOIN dbo.Staff stf ON stf.StaffID = c.PrimaryTeacherID
JOIN dbo.StaffClassification sfc ON sfc.StaffClassificationID = stf.PrimaryClassificationID
LEFT OUTER JOIN dbo.[User] u ON u.UserID = stf.UserID


Do you spot a mistake on my part? I'm not very experienced with SQL statements, so any help is appreciated.

Answer

Try casting stf.StaffNumber to a VARCHAR before appending to it:

CAST(stf.StaffNumber AS VARCHAR)

The same applies for any numeric values that you're appending a string to.

EDIT

I believe this should cover everything:

SELECT '"'+    
CAST(stf.StaffNumber AS VARCHAR) +'",'+    
CAST(stf.PrimarySchoolID AS VARCHAR) +',"'+    
stf.LastName +'","'+    
stf.FirstName +'","' +    
ISNULL(u.username, '')+'","'+    
ISNULL(sfc.[NAME],'') +'",'+    
CAST(stf.PrimarySchoolID AS VARCHAR)  

On a side note, you should use ISNULL in cases where there's only one condition. COALESCE will work fine, but it's wired for multiple conditions, i.e. COALESCE(Col1, Col2, Col3, Col4)