[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'INSERT INTO tProjectDetailReportData (ProjDesc, ProjAnticCompDate, ProjManager, ProjSponsor, ProjType, ProjDept) SELECT tProject.ProjDesc, tProject.ProjAnticCompDate, tProjectManager.ProjectManagerName, tProjectSponsor.ProjectSponsorName, tProjectType.ProjectType, tDepartment.Department FROM (((tDepartment RIGHT JOIN tProject ON tDepartment.Project... 

CREATE PROCEDURE spProjectDetailReport
(
@ProjectType Int,
@OrderBy varchar(50)
)

AS

DECLARE @SQL varchar(255)

SET @SQL = 'INSERT INTO tProjectDetailReportData (ProjDesc, ProjAnticCompDate, ProjManager, ProjSponsor, ProjType, ProjDept)
SELECT tProject.ProjDesc, tProject.ProjAnticCompDate, tProjectManager.ProjectManagerName, tProjectSponsor.ProjectSponsorName, tProjectType.ProjectType, tDepartment.Department 
FROM (((tDepartment RIGHT JOIN tProject 
ON tDepartment.ProjectDeptID = tProject.ProjDept) 
LEFT JOIN tProjectManager 
ON tProject.ProjManagerID = tProjectManager.ProjectManagerID) 
LEFT JOIN tProjectSponsor 
ON tProject.ProjSponsorID = tProjectSponsor.ProjectSponsorID) 
LEFT JOIN tProjectType ON tProject.ProjTypeID = tProjectType.ProjectTypeID 
WHERE (((tProjectType.ProjectTypeID) =' + @ProjectType + 'OR' + @ProjectType + '= 0))  
ORDER BY' + @OrderBy

EXEC(@SQL)
GO

quote:

---

Originally posted by Gyto

Hi there,

Whenever I run this stored procedure I get the error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'INSERT INTO tProjectDetailReportData (ProjDesc, ProjAnticCompDate, ProjManager, ProjSponsor, ProjType, ProjDept) SELECT tProject.ProjDesc, tProject.ProjAnticCompDate, tProjectManager.ProjectManagerName, tProjectSponsor.ProjectSponsorName, tProjectType.ProjectType, tDepartment.Department FROM (((tDepartment RIGHT JOIN tProject ON tDepartment.Project... 

The SP code....

CREATE PROCEDURE spProjectDetailReport
(
@ProjectType Int,
@OrderBy varchar(50)
)

AS

DECLARE @SQL varchar(8000)

SET @SQL = 'INSERT INTO tProjectDetailReportData (ProjDesc, ProjAnticCompDate, ProjManager, ProjSponsor, ProjType, ProjDept)
SELECT tProject.ProjDesc, tProject.ProjAnticCompDate, tProjectManager.ProjectManagerName, tProjectSponsor.ProjectSponsorName, tProjectType.ProjectType, tDepartment.Department 
FROM (((tDepartment RIGHT JOIN tProject 
ON tDepartment.ProjectDeptID = tProject.ProjDept) 
LEFT JOIN tProjectManager 
ON tProject.ProjManagerID = tProjectManager.ProjectManagerID) 
LEFT JOIN tProjectSponsor 
ON tProject.ProjSponsorID = tProjectSponsor.ProjectSponsorID) 
LEFT JOIN tProjectType ON tProject.ProjTypeID = tProjectType.ProjectTypeID 
WHERE (((tProjectType.ProjectTypeID) =' + @ProjectType + 'OR' + @ProjectType + '= 0))  
ORDER BY' + @OrderBy

EXEC(@SQL)
GO

Apparently I have to do it this way in order to use the 'Order By' clause at the end, but ever since I changed it I've had that error.....any ideas why?

Thanks

Matt

---

WHERE (((tProjectType.ProjectTypeID) =' + CONVERT(varchar(20), @ProjectType) + ' OR ' + CONVERT(varchar(20), @ProjectType) + '= 0))