Hi, I got the error message says that " Msg 245, Level 16, State 1, Line 127 Conversion failed when converting the varchar value 'Systemation' to data type int. I attached the code USE [dbMarketing] GO /****** Object: StoredProcedure [ui].[spNationalTESReportVerbatimsFacilitator] Script Date: 03/02/2012 09:55:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --This is the main details for the SP for the NPS report /*Name: spNPSReportSummaryDetails Description: SP for the AD listing on the NPS Summary Report Author: Mark Poirot Description Date Changed By ALTERd procedure 2/7/2009 Mark Poirot */ /* Exec [ui].[spNPSReportSummaryDetails] @CommaDelimitedString = 'New Psychology of Selling-NPOS', @CourseStartParameter= '1/1/2009', @CourseEndParameter = '4/1/2009' *//* ALTER PROCEDURE [ui].[spNationalTESReportVerbatimsFacilitator] --DECLARE @CourseNameParameter VARCHAR(200) @CommaDelimitedString VARCHAR (max), @CourseStartParameter SMALLDATETIME, @CourseEndParameter SMALLDATETIME, @StartValueScore TinyInt, @EndValueScore TinyInt, @SupervisorLevelParameter VarChar(Max), @NTIDArea Varchar(20) AS SET NOCOUNT ON */ DECLARE @CommaDelimitedString VARCHAR (max) DECLARE @CourseStartParameter SMALLDATETIME DECLARE @CourseEndParameter SMALLDATETIME Declare @StartValueScore TinyInt declare @EndValueScore TinyInt declare @SupervisorLevelParameter VarChar(Max) DECLARE @NTIDArea Varchar (20) DECLARE @NationalTESEmployeeCourseVarSUP TABLE (SurveyFacilitatorName INT, SurveyLocationName VARCHAR(200)) DECLARE @NationalTESEmployeeCourseVarCalc TABLE (CalcFacilitator varchar (20), CalcCourseDate SMALLDATETIME, CalcCourseName VARCHAR(200), CalcCommentScore VARCHAR(10), CalcLocationName VARCHAR(200), CalcManagementScore Varchar(10), CalcPresentationScore Varchar(10), CalcEngagementScore Varchar(10), CalcExplanationScore Varchar(10), CalcJobSkillsScore Varchar(10), CalcImprovementScore Varchar(4000), CalcLearningObjectives Varchar(10),/* CalcLogicallyOrganized Varchar(10),*/ CalcLearningEffectiveness Varchar(10), CalcTimeToLearn Varchar(10), CalcJobEffectiveness Varchar(10), CalcIncreasedSuccess Varchar(10) ) SET @CommaDelimitedString = 'Fraud Awareness: Classroom Training' SET @CourseStartParameter = '2/1/2012' SET @CourseEndParameter = '2/24/2012' SET @StartValueScore = '1' SET @EndValueScore = '5' SET @SupervisorLevelParameter = '110975' SET @NTIDArea = 'foxga' Declare @AreaFilter Varchar(20) set @AreaFilter = (select AreaID from dbEmployee.Summary.tblEmployeeSnapshot WITH (NOLOCK) where networkID = @NTIDArea) DECLARE @FacilitatorEmpID INT SET @FacilitatorEmpId = (Select EmployeeID from dbEmployee.Summary.tblEmployeeSnapshot where NetworkID = @NTIDArea) /* Test Section for the comma delimited string */ DECLARE @CharCount INT DECLARE @CommaDelimitedResults TABLE (CommaDelimitedString VARCHAR (max)) --SET @CommaDelimitedString = 'Blackberry Storm,Foundations for Business' -- Add a comma to the end if it is not there already: IF (SELECT SUBSTRING (@CommaDelimitedString, LEN (@CommaDelimitedString), 1)) <> ',' BEGIN SET @CommaDelimitedString = @CommaDelimitedString + ',' END -- ALTER a table to hold results after the data is parsed: -- Loop and parse the comma delimited string(s): WHILE (SELECT LEN (@CommaDelimitedString)) >= 1 BEGIN SET @CharCount = CHARINDEX (',', @CommaDelimitedString, 1) INSERT INTO @CommaDelimitedResults SELECT REPLACE (SUBSTRING (@CommaDelimitedString, 1, @CharCount), ',', '') SET @CommaDelimitedString = SUBSTRING (@CommaDelimitedString, @CharCount + 1, 50000) END --SELECT * FROM @CommaDelimitedResults ---------------------------------------------------------------------------------------------------------- /* Test Section for the comma delimited string of Supervisor Ids*/ DECLARE @CharCountIDs INT DECLARE @CommaDelimitedResultsIDs TABLE (SupervisorID VARCHAR (max)) --SET @CommaDelimitedString = 'Blackberry Storm,Foundations for Business' -- Add a comma to the end if it is not there already: IF (SELECT SUBSTRING (@SupervisorLevelParameter, LEN (@SupervisorLevelParameter), 1)) <> ',' BEGIN SET @SupervisorLevelParameter = @SupervisorLevelParameter + ',' END -- ALTER a table to hold results after the data is parsed: -- Loop and parse the comma delimited string(s): WHILE (SELECT LEN (@SupervisorLevelParameter)) >= 1 BEGIN SET @CharCountIDs = CHARINDEX (',', @SupervisorLevelParameter, 1) INSERT INTO @CommaDelimitedResultsIDs SELECT REPLACE (SUBSTRING (@SupervisorLevelParameter, 1, @CharCountIDs), ',', '') SET @SupervisorLevelParameter = SUBSTRING (@SupervisorLevelParameter, @CharCountIDs + 1, 30000) END --SELECT * FROM @CommaDelimitedResultsIDs /* This is the code that will determine the distinct facilitator that has taught class X, this will determine the comparison for the rest of the information*/ INSERT INTO @NationalTESEmployeeCourseVarSUP (SurveyFacilitatorName, SurveyLocationName) (SELECT DISTINCT Facilitator1, LocationName FROM Detail.tblNationalTESSurvey as A, @CommaDelimitedResults as B WHERE (a.CourseName = b.CommaDelimitedString) AND (CourseDate BETWEEN @CourseStartParameter AND @CourseEndParameter)) UNION (SELECT DISTINCT Facilitator2, LocationName FROM Detail.tblNationalTESSurvey as A, @CommaDelimitedResults as B WHERE (a.CourseName = b.CommaDelimitedString) AND (CourseDate BETWEEN @CourseStartParameter AND @CourseEndParameter)) UNION (SELECT DISTINCT Facilitator3, LocationName FROM Detail.tblNationalTESSurvey as A, @CommaDelimitedResults as B WHERE (a.CourseName = b.CommaDelimitedString) AND (CourseDate BETWEEN @CourseStartParameter AND @CourseEndParameter)) --select * from @NationalTESEmployeeCourseVarSUP /* This is the code that will determine the facilitator score for course X and just for the specific instance, this will determine the comparison for the rest of the information*/ INSERT INTO @NationalTESEmployeeCourseVarCalc (CalcFacilitator, CalcCourseDate, CalcCourseName, CalcLocationName, CalcManagementScore, CalcPresentationScore, CalcEngagementScore, CalcExplanationScore, CalcJobSkillsScore, CalcImprovementScore, CalcLearningObjectives,/* CalcLogicallyOrganized,*/ CalcLearningEffectiveness, CalcTimeToLearn, CalcJobEffectiveness, CalcIncreasedSuccess) (SELECT Facilitator1, CourseDate, CourseName, LocationName, FirstFacilitatorManagement, FirstFacilitatorPresentation, FirstFacilitatorEngagement, FirstFacilitatorExplanation, FirstFacilitatorJobSkills, FirstFacilitatorImprovement, CourseLearningObjectives,/* LogicallyOrganized,*/ LearningEffectiveness, TimeToLearn, JobEffectiveness, IncreasedSuccess FROM Detail.tblNationalTESSurvey as A inner Join @CommaDelimitedResults as B ON /*WHERE*/ (a.CourseName = b.CommaDelimitedString) AND (a.CourseDate BETWEEN @CourseStartParameter AND @CourseEndParameter)) UNION all (SELECT Facilitator2, CourseDate, CourseName, LocationName, SecondFacilitatorManagement, SecondFacilitatorPresentation, SecondFacilitatorEngagement, SecondFacilitatorExplanation, SecondFacilitatorJobSkills, SecondFacilitatorImprovement, CourseLearningObjectives,/* LogicallyOrganized,*/ LearningEffectiveness, TimeToLearn, JobEffectiveness, IncreasedSuccess FROM Detail.tblNationalTESSurvey as A inner Join @CommaDelimitedResults as B ON /*WHERE*/ (a.CourseName = b.CommaDelimitedString) AND (CourseDate BETWEEN @CourseStartParameter AND @CourseEndParameter)) UNION all (SELECT Facilitator3, CourseDate, CourseName, LocationName, ThirdFacilitatorManagement, ThirdFacilitatorPresentation, ThirdFacilitatorEngagement, ThirdFacilitatorExplanation, ThirdFacilitatorJobSkills, ThirdFacilitatorImprovement, CourseLearningObjectives,/* LogicallyOrganized,*/ LearningEffectiveness, TimeToLearn, JobEffectiveness, IncreasedSuccess FROM Detail.tblNationalTESSurvey as A inner Join @CommaDelimitedResults as B ON /*WHERE*/ (a.CourseName = b.CommaDelimitedString) AND (CourseDate BETWEEN @CourseStartParameter AND @CourseEndParameter)) --Select * from @NationalTESEmployeeCourseVarCalc /* This is the block of code that determines the heirarchy of the dataset by AD/Sup/Fac */ Declare @BaseCalcTable table ( BaseCalcDirector VARCHAR(300), BaseCalcFacilitator VARCHAR(300), BaseCalcFacilitatorID VARCHAR(20), BaseCalcDirectorID VARCHAR(20), BaseCalcADName VARCHAR(300), BaseCalcADID VARCHAR(20), BaseCalcManagerID VARCHAR(20), BaseCalcManagerName VARCHAR(300), BaseCalcSupID VARCHAR(20), BaseCalcSupName VARCHAR(300), BaseCalcCourseName VARCHAR(300), BaseCalcCourseDate SMALLDATETIME, BaseCalcLocationName VARCHAR(300), BaseCalcCourseLearningObjectives Varchar(10), --BaseCalcCourseLogicallyOrganized varchar (10), BaseCalcCourseLearningEffectiveness varchar (10), BaseCalcCourseTimeToLearn varchar(10), BaseCalcCourseJobEffectiveness varchar (10), BaseCalcCourseIncreasedSuccess Varchar (10), BaseCalcFacilitatorManagementScore Varchar(10), BaseCalcFacilitatorScoreCount Varchar(10), BaseCalcFacilitatorPresentationScore Varchar(10), BaseCalcFacilitatorEngagementScore Varchar(10), BaseCalcFacilitatorExplanationScore Varchar(10), BaseCalcFacilitatorJobSkillsScore Varchar(10), BaseCalcFacilitatorImprovementScore Varchar(4000) ) Insert into @BaseCalcTable( BaseCalcDirector, BaseCalcFacilitator, BaseCalcFacilitatorID, BaseCalcDirectorID, BaseCalcADName, BaseCalcADID, BaseCalcManagerID, BaseCalcManagerName, BaseCalcSupID, BaseCalcSupName, BaseCalcCourseName, BaseCalcCourseDate, BaseCalcLocationName, BaseCalcCourseLearningObjectives, --BaseCalcCourseLogicallyOrganized, BaseCalcCourseLearningEffectiveness, BaseCalcCourseTimeToLearn, BaseCalcCourseJobEffectiveness, BaseCalcCourseIncreasedSuccess, BaseCalcFacilitatorManagementScore, BaseCalcFacilitatorScoreCount, BaseCalcFacilitatorPresentationScore, BaseCalcFacilitatorEngagementScore, BaseCalcFacilitatorExplanationScore, BaseCalcFacilitatorJobSkillsScore, BaseCalcFacilitatorImprovementScore ) SELECT Level24Name, dbo.udfEmployeeIDtoEmployeeName(SurveyFacilitatorName) AS FacilitatorName, SurveyFacilitatorName, Level24ID, Level20Name, Level20ID, Level16ID, Level16Name, Level04ID, Level04Name, CalcCourseName, CalcCourseDate, CalcLocationName, CalcLearningObjectives, --CalcLogicallyOrganized, CalcLearningEffectiveness, CalcTimeToLearn, CalcJobEffectiveness, CalcIncreasedSuccess, ------------------------------------------------------------------------------------------------------------- --New Questions ------------------------------------------------------------------------------------------------------------- CalcManagementScore, Case When CalcManagementScore != 0 Then 1 Else 0 End as FacCount, CalcPresentationScore, CalcEngagementScore, CalcExplanationScore, CalcJobSkillsScore, CalcImprovementScore FROM @NationalTESEmployeeCourseVarSUP AS a inner join (Select Employeeid, Level24ID, Level24Name, Level20Name, Level20ID, Level16ID, Level16Name, Level04ID, Level04Name, AreaID from dbEmployee.Summary.tblEmployeeSnapshot WITH (NOLOCK), @CommaDelimitedResultsIDs as d Where ((Level24ID = d.supervisorID) Or (Level20ID = d.supervisorID) OR (Level16ID = d.supervisorID) Or (Level04ID = d.supervisorID)) Group by Employeeid, Level24ID,Level24Name ,Level20Name,Level20ID, Level16ID,Level16Name,Level04ID,Level04Name,AreaID) as b on a.SurveyFacilitatorName = b.EmployeeID inner join @NationalTESEmployeeCourseVarCalc as c on a.SurveyFacilitatorName = c.CalcFacilitator Where c.CalcLocationName = a.SurveyLocationName And b.AreaID = @AreaFilter --select @AreaFilter --select * from @BaseCalcTable Declare @AveTable table ( AveCalcDirector VARCHAR(300), AveCalcFacilitator VARCHAR(300), AveCalcFacilitatorID VARCHAR(20), AveCalcDirectorID VARCHAR(20), AveCalcADName VARCHAR(300), AveCalcADID VARCHAR(20), AveCalcManagerID VARCHAR(20), AveCalcManagerName VARCHAR(300), AveCalcSupID VARCHAR(20), AveCalcSupName VARCHAR(300), AveCalcCourseName VARCHAR(300), AveCalcCourseDate SMALLDATETIME, AveCalcLocationName VARCHAR(300), OverallAveScore int, OverallAveCourseScore int, AveCalcCourseLearningObjectives Varchar(10), --AveCalcCourseLogicallyOrganized Varchar(10), AveCalcCourseLearningEffectiveness Varchar(10), AveCalcCourseTimeToLearn Varchar(10), AveCalcCourseJobEffectiveness Varchar(10), AveCalcCourseIncreasedSuccess Varchar(10), AveCalcFacilitatorManagementScore Varchar(10), AveFacilitatorScoreCount Varchar(10), AveCalcFacilitatorPresentationScore Varchar(10), AveCalcFacilitatorEngagementScore Varchar(10), AveCalcFacilitatorExplanationScore Varchar(10), AveCalcFacilitatorJobSkillsScore Varchar(10), AveCalcFacilitatorImprovementScore Varchar(4000) ) Insert into @AveTable ( AveCalcDirector, AveCalcFacilitator, AveCalcFacilitatorID, AveCalcDirectorID, AveCalcADName, AveCalcADID, AveCalcManagerID, AveCalcManagerName, AveCalcSupID, AveCalcSupName, AveCalcCourseName, AveCalcCourseDate, AveCalcLocationName, OverallAveScore, OverallAveCourseScore, AveCalcCourseLearningObjectives, --AveCalcCourseLogicallyOrganized, AveCalcCourseLearningEffectiveness, AveCalcCourseTimeToLearn, AveCalcCourseJobEffectiveness, AveCalcCourseIncreasedSuccess, AveCalcFacilitatorManagementScore, AveFacilitatorScoreCount, AveCalcFacilitatorPresentationScore, AveCalcFacilitatorEngagementScore, AveCalcFacilitatorExplanationScore, AveCalcFacilitatorJobSkillsScore, AveCalcFacilitatorImprovementScore ) select BaseCalcDirector, BaseCalcFacilitator, BaseCalcFacilitatorID, BaseCalcDirectorID, BaseCalcADName, BaseCalcADID, BaseCalcManagerID, BaseCalcManagerName, BaseCalcSupID, BaseCalcSupName, BaseCalcCourseName, BaseCalcCourseDate, BaseCalcLocationName, (cast(BaseCalcFacilitatorManagementScore as int) + cast(BaseCalcFacilitatorPresentationScore as int) + cast(BaseCalcFacilitatorEngagementScore as int) + cast(BaseCalcFacilitatorExplanationScore as int) + cast(BaseCalcFacilitatorJobSkillsScore as int)) / 5 as AveScore, (/*cast(BaseCalcCourseLogicallyOrganized as int) + */cast(BaseCalcCourseLearningObjectives as int) + cast(BaseCalcCourseLearningEffectiveness as int) + cast(BaseCalcCourseTimeToLearn as int) + cast(BaseCalcCourseJobEffectiveness as int) + cast(BaseCalcCourseIncreasedSuccess as int)) / 6 as AveCourseScore, BaseCalcCourseLearningObjectives, --BaseCalcCourseLogicallyOrganized, BaseCalcCourseLearningEffectiveness, BaseCalcCourseTimeToLearn, BaseCalcCourseJobEffectiveness, BaseCalcCourseIncreasedSuccess, BaseCalcFacilitatorManagementScore, BaseCalcFacilitatorScoreCount, BaseCalcFacilitatorPresentationScore, BaseCalcFacilitatorEngagementScore, BaseCalcFacilitatorExplanationScore, BaseCalcFacilitatorJobSkillsScore, BaseCalcFacilitatorImprovementScore from @BaseCalcTable --select * from @BaseCalcTable select * from @AveTable where OverallAveScore between @StartValueScore and @EndValueScore /* select AveCalcDirector, AveCalcFacilitator, AveCalcFacilitatorID, AveCalcDirectorID, AveCalcADName, AveCalcADID, AveCalcManagerID, AveCalcManagerName, AveCalcSupID, AveCalcSupName, AveCalcCourseName, AveCalcCourseDate, AveCalcFacilitatorScore, AveCalcLocationName, OverallAveScore, case when OverallAveScore <= 6 Then 1 Else 0 End as OverallDetractor, case when OverallAveScore > 6 and OverallAveScore <= 8 Then 1 Else 0 End as OverallPassive, case when OverallAveScore > 8 Then 1 Else 0 End as OverallPromoter, AveCalcCourseScore, case when OverallAveCourseScore <= 6 Then 1 Else 0 End as OverallCourseDetractor, case when OverallAveCourseScore > 6 and OverallAveCourseScore <= 8 Then 1 Else 0 End as OverallCoursePassive, case when OverallAveCourseScore > 8 Then 1 Else 0 End as OverallCoursePromoter, AveCalcCourseLearningObjectives, AveCalcFacilitatorManagementScore, AveCalcFacilitatorBalanceScore, AveCalcFacilitatorPracticeScore, AveCalcFacilitatorApplicationScore from @AveTable ----------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------*/ --select * from @NEATNPSEmployeeCourseVarSUP --select * from @NEATNPSEmployeeCourseVarCalc --WHERE --(a.SurveyFacilitatorName = (b.EmployeeID)) AND (c.CalcFacilitator = (b.EmployeeID)) AND (a.SurveyFacilitatorName = (c.CalcFacilitator)) --ORDER BY SurveyFacilitatorName --Attempts at getting the count for the faciltators, passives, and detractors /* SELECT COUNT(CalcFacilitatorScore) FROM @NEATNPSEmployeeCourseVarCalc WHERE (CalcCourseName='test 3') AND (CalcCourseDate = '1/27/2009') AND (CalcFacilitator = '309296') AND(CalcFacilitatorScore BETWEEN '0' AND '6') SELECT COUNT(CalcFacilitatorScore) FROM @NEATNPSEmployeeCourseVarCalc WHERE (CalcCourseName='test 3') AND (CalcCourseDate = '1/27/2009') AND (CalcFacilitator = '309296') AND(CalcFacilitatorScore BETWEEN '7' AND '8') SELECT COUNT(CalcFacilitatorScore) FROM @NEATNPSEmployeeCourseVarCalc as a, detail.tblSurvey as b WHERE (a.CalcCourseName=b.CourseName) AND (CalcCourseDate = b.CourseDate) AND (CalcFacilitator = b.Facilitator1) AND (CalcFacilitatorScore BETWEEN '0' AND '6') /*SELECT DISTINCT Level16ID, Level16Name FROM @NEATNPSEmployeeCourseVarSUP AS a, dbEmployee.Summary.tblEmployeeSnapshot AS b WITH (NOLOCK) WHERE (a.SurveyFacilitatorName = (b.EmployeeID)) AND (b.Level20ID = ('199394'))*/ */ --Select * from #NEATNPSEmployeeCourseVar --Select * --from detail.tblsurvey --where coursename='test 3' /* @CourseNameParameter VARCHAR(200), @CourseStartParameter SMALLDATETIME, @CourseEndParameter SMALLDATETIME AS DECLARE @NEATNPSEmployeeCourseVarSUP TABLE (SurveyFacilitatorName INT, SurveyFacilitatorCourseName VARCHAR(200), SurveyFacilitatorCourseDate SMALLDATETIME, SurveyFacilitatorLocationName VARCHAR(200)) DECLARE @NEATNPSEmployeeCourseVarCalc TABLE (CalcFacilitator INT, CalcCourseDate SMALLDATETIME, CalcCourseName VARCHAR(200), CalcFacilitatorScore INT, CalcCommentScore INT) /* This is the code that will determine the distinct facilitator that has taught class X, this will determine the comparison for the rest of the information*/ INSERT INTO @NEATNPSEmployeeCourseVarSUP (SurveyFacilitatorName, SurveyFacilitatorCourseName, SurveyFacilitatorCourseDate, SurveyFacilitatorLocationName) (SELECT DISTINCT Facilitator1, CourseName, CourseDate, LocationName FROM Detail.tblSurvey WHERE (CourseName IN (@CourseNameParameter)) AND (CourseDate BETWEEN @CourseStartParameter AND @CourseEndParameter)) UNION (SELECT DISTINCT Facilitator2, CourseName, CourseDate, LocationName FROM Detail.tblSurvey WHERE (CourseName IN (@CourseNameParameter)) AND (CourseDate BETWEEN @CourseStartParameter AND @CourseEndParameter)) UNION (SELECT DISTINCT Facilitator3, CourseName, CourseDate, LocationName FROM Detail.tblSurvey WHERE (CourseName IN (@CourseNameParameter)) AND (CourseDate BETWEEN @CourseStartParameter AND @CourseEndParameter)) /* This is the code that will determine the facilitator score for course X and just for the specific instance, this will determine the comparison for the rest of the information*/ INSERT INTO @NEATNPSEmployeeCourseVarCalc (CalcFacilitator, CalcCourseDate, CalcCourseName, CalcFacilitatorScore, CalcCommentScore) (SELECT Facilitator1, CourseDate, CourseName, FirstFacilitatorScore, ContentScore FROM Detail.tblSurvey WHERE (CourseName IN (@CourseNameParameter)) AND (CourseDate BETWEEN @CourseStartParameter AND @CourseEndParameter)) UNION ALL (SELECT Facilitator2, CourseDate, CourseName, SecondFacilitatorScore, ContentScore FROM Detail.tblSurvey WHERE (CourseName IN (@CourseNameParameter)) AND (CourseDate BETWEEN @CourseStartParameter AND @CourseEndParameter)) UNION ALL (SELECT Facilitator3, CourseDate, CourseName, ThirdFacilitatorScore, ContentScore FROM Detail.tblSurvey WHERE (CourseName IN (@CourseNameParameter)) AND (CourseDate BETWEEN @CourseStartParameter AND @CourseEndParameter)) --Select * from #NEATNPSEmployeeCourseVar /* This is the total query block that will determine the overall dataset*/ /*SELECT DISTINCT SurveyFacilitatorName, CourseName, CourseDate, ContentComment, (select count(ContentScore) from detail.tblSurvey where (ContentScore Between '0' AND '6') AND (coursename = 'test 2' AND CourseDate = '1/19/2009')) AS DetractorCount, (select count(ContentScore) from detail.tblSurvey where (ContentScore Between '9' AND '10') AND (coursename = 'test 2' AND CourseDate = '1/19/2009')) AS PromoterCount, JobEffectivenessComment, --(select count() from detail.tblSurvey where (ContentScore Between '0' AND '6') AND (coursename = 'test 2' AND CourseDate = '1/19/2009')) AS DetractorFacilitatorCount, --(select count(ContentScore) from detail.tblSurvey where (ContentScore Between '0' AND '6') AND (coursename = 'test 2' AND CourseDate = '1/19/2009')) AS PromoterFacilitatorCount, DeliveryMethodPreferred, AdditionalComments FROM @NEATNPSEmployeeCourseVarSUP AS a, detail.tblSurvey as b WHERE (((a.SurveyFacilitatorName = b.Facilitator1) OR (a.SurveyFacilitatorName = b.Facilitator2) OR (a.SurveyFacilitatorName = b.Facilitator3)) AND ((b.CourseName = 'test 2') AND (b.CourseDate = '1/19/2009'/*BETWEEN '1/1/2009' AND '3/1/2009'*/)))*/ /* This is the block of code that determines the heirarchy of the dataset by AD/Sup/Fac */ SELECT DISTINCT Level36Name, dbo.udfEmployeeIDtoEmployeeName(SurveyFacilitatorName) AS FacilitatorName, SurveyFacilitatorName, Level24ID, Level20Name, Level20ID, Level16ID, Level16Name, Level04ID, Level04Name, SurveyFacilitatorCourseName, SurveyFacilitatorCourseDate, SurveyFacilitatorLocationName, CalcFacilitatorScore, Case When CalcFacilitatorScore <= 6 Then 1 Else 0 End as FacilitatorDetractor, Case When CalcFacilitatorScore >6 and CalcFacilitatorScore <=8 Then 1 Else 0 End as FacilitatorPassive, Case When CalcFacilitatorScore >8 Then 1 Else 0 End as FacilitatorPromoter, CalcCommentScore, Case When CalcCommentScore <= 6 Then 1 Else 0 End as CourseDetractor, Case When CalcCommentScore >6 and CalcCommentScore <=8 Then 1 Else 0 End as CoursePassive, Case When CalcCommentScore >8 Then 1 Else 0 End as CoursePromoter -- (select count(ContentScore) from detail.tblSurvey where (ContentScore Between '0' AND '6') AND (coursename = 'test 2' AND CourseDate = '1/19/2009')) AS DetractorCount, -- (select count(ContentScore) from detail.tblSurvey where ((ContentScore Between '9' AND '10') AND (coursename = 'test 2') AND CourseDate between '1/1/2009' and '2/3/2009')) AS PromoterCount --(select count(CalcFacilitatorScore) from @NEATNPSEmployeeCourseVarCalc AS where (ContentScore Between '0' AND '6') AND (coursename = 'test 2' AND CourseDate = '1/19/2009')) AS DetractorCount, -- (select count(ContentScore) from detail.tblSurvey where (ContentScore Between '9' AND '10') AND (coursename = 'test 2' AND CourseDate = '1/19/2009')) AS PromoterCount FROM @NEATNPSEmployeeCourseVarSUP AS a, dbEmployee.Summary.tblEmployeeSnapshot AS b WITH (NOLOCK), @NEATNPSEmployeeCourseVarCalc as c WHERE ((a.SurveyFacilitatorName = (b.EmployeeID)) AND (c.CalcFacilitator = (b.EmployeeID)) AND (a.SurveyFacilitatorCourseDate = c.CalcCourseDate)) --Attempts at getting the count for the faciltators, passives, and detractors /* SELECT COUNT(CalcFacilitatorScore) FROM @NEATNPSEmployeeCourseVarCalc WHERE (CalcCourseName='test 3') AND (CalcCourseDate = '1/27/2009') AND (CalcFacilitator = '309296') AND(CalcFacilitatorScore BETWEEN '0' AND '6') SELECT COUNT(CalcFacilitatorScore) FROM @NEATNPSEmployeeCourseVarCalc WHERE (CalcCourseName='test 3') AND (CalcCourseDate = '1/27/2009') AND (CalcFacilitator = '309296') AND(CalcFacilitatorScore BETWEEN '7' AND '8') SELECT COUNT(CalcFacilitatorScore) FROM @NEATNPSEmployeeCourseVarCalc as a, detail.tblSurvey as b WHERE (a.CalcCourseName=b.CourseName) AND (CalcCourseDate = b.CourseDate) AND (CalcFacilitator = b.Facilitator1) AND (CalcFacilitatorScore BETWEEN '0' AND '6') /*SELECT DISTINCT Level16ID, Level16Name FROM @NEATNPSEmployeeCourseVarSUP AS a, dbEmployee.Summary.tblEmployeeSnapshot AS b WITH (NOLOCK) WHERE (a.SurveyFacilitatorName = (b.EmployeeID)) AND (b.Level20ID = ('199394'))*/ */ --Select * from #NEATNPSEmployeeCourseVar --Select * --from detail.tblsurvey --where coursename='test 3' */ "

I presume you are getting this error when you are passing parameters from a report to this stored procedure... Check the data types of the parameters you have for the report to be matching correctly with the mapped parameters of the dataset.