Q: 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.

A: 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)

A: Change the last line of the SELECT to:
CAST(stf.PrimarySchoolID as varchar)

A: You'll want to CAST stf.PrimarySchoolID to a character type.
...
CAST(stf.PrimarySchoolID AS VARCHAR(20)) -- You can fix length as needed
...


A: You need to cast stf.PrimarySchoolID to a character type (say, varchar) before using it in a concatenation:
SET NOCOUNT ON;

SELECT '"'+
    stf.StaffNumber +'",'+
    convert(varchar, stf.PrimarySchoolID) +',"'+
    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

You can do type cast either using SQL Server's CONVERT or SQL ANSI's CAST.

A: Since stf.PrimarySchoolID is an int and you are trying to concatenate it to a varchar() field, you are getting this error. Try the foll:
SELECT '"'+
    stf.StaffNumber +'",'+
    cast(stf.PrimarySchoolID as varchar(10)) +',"'+
    stf.LastName +'","'+
    stf.FirstName +'","' +
    COALESCE(u.username, '')+'","'+
    COALESCE(sfc.[NAME],'') +'",'+
    stf.PrimarySchoolID


A: Joining tables columns may not be matching or joining on given columns. Double check on what columns, logic are given to join.
