joshmax joshmax - 1 year ago 72
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 Source

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)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download