title: "Error converting data type nvarchar to numeric."
body: <p>I'm trying to create a function that returns a table with a bunch of fields.
  The query used in the function inserts data to a temp table which then inserts into the returning table.
  query runs fine when I run it outside of the function but when I select from the function I get "Error converting data type nvarchar to numeric." error. </p>
  
  <p>The structure for the returning table is the same is the table which selects and inserts into the returning table. Below is how the function is defined</p>
  
  <pre><code>ALTER  FUNCTION [dbo].[Func_PrescriberData](@NetworkID Bigint, @StartDate datetime, @EndDate datetime)
  
  RETURNS  @rtnTable TABLE 
  (
   Name nvarchar(1000) null,ClinicID int null ,NetworkPrescriberID int Null,GuideMedPatients int  NULL, InactivePatients int NULL,AvgAge decimal  NULL,AvgPHQScore decimal  NULL,
   [%RiskAssessmentHigh] varchar(10) NULL ,[%RiskAssessmentModerate] varchar(10)  NULL,[%RiskAssessmentLow] varchar(10)  NULL,
   ToxicologyTests int  NULL,[%ConsistentResults] varchar(10) NULL, [%Alcohol] varchar(10) NULL,[%NegativePrescribed] varchar(10) NULL
   ,[%Illicit] varchar(10)  NULL,TotalPDMPs int  NULL,[%AberrantResults] varchar(10)  NULL, CSAReviewed int  NULL,Type nvarchar(1000) null ,medlesstan15 int NULL,between15and59 int NULL,between60and100 int NULL,between101and500 int NULL,greaterthan500 int NULL, avgmed decimal NULL, [BH/AM] int NULL,OpiodsBelowThreshold int NULL,OpioidPreScreening int NULL,SedativesAntiAnxiety int NULL,Stimulants int NULL,Other int NULL) 
  AS
  BEGIN 
  DECLARE @TempTable table
  (
   Name nvarchar(1000) null,ClinicID int null ,NetworkPrescriberID int Null,GuideMedPatients int  NULL, InactivePatients int NULL,AvgAge decimal  NULL,AvgPHQScore decimal  NULL,
   [%RiskAssessmentHigh] varchar(10) NULL ,[%RiskAssessmentModerate] varchar(10)  NULL,[%RiskAssessmentLow] varchar(10)  NULL,
   ToxicologyTests int  NULL,[%ConsistentResults] varchar(10) NULL, [%Alcohol] varchar(10) NULL,[%NegativePrescribed] varchar(10) NULL
   ,[%Illicit] varchar(10)  NULL,TotalPDMPs int  NULL,[%AberrantResults] varchar(10)  NULL, CSAReviewed int  NULL,Type nvarchar(1000) null ,medlesstan15 int NULL,between15and59 int NULL,between60and100 int NULL,between101and500 int NULL,greaterthan500 int NULL, avgmed decimal NULL, [BH/AM] int NULL,OpiodsBelowThreshold int NULL,OpioidPreScreening int NULL,SedativesAntiAnxiety int NULL,Stimulants int NULL,Other int NULL) 
  insert into @TempTable (
  Name ,ClinicID,NetworkPrescriberID,GuideMedPatients, InactivePatients ,AvgAge ,AvgPHQScore ,[%RiskAssessmentHigh],[%RiskAssessmentModerate] ,[%RiskAssessmentLow]
   ,ToxicologyTests ,[%ConsistentResults], [%Alcohol] ,[%NegativePrescribed] ,[%Illicit],TotalPDMPs ,[%AberrantResults],
   CSAReviewed ,Type,medlesstan15,between15and59,between60and100,between101and500,greaterthan500,avgmed,[BH/AM],OpiodsBelowThreshold,OpioidPreScreening,SedativesAntiAnxiety,Stimulants,Other
  </code></pre>
  
  <p>It's probably hard to tell what the issue is from the above script but I'm looking for a way to debug where the issue is.
  Strange thing is the function runs fine if I change one of the parameters (change NetworkID) which is supposed to return data in the same format.</p>
  
  <p>Can someone help me point to where to look?</p>
  
  <p>Thanks,</p>
  
  <p>I've found a link to post the entire sql script. It can be found <a href="http://www.tutorialspoint.com/execute_sql_online.php?PID=0Bw_CjBb95KQMclFFaTZ2azVlMEk" rel="nofollow noreferrer">here</a></p>