CODEWITHSUNDEEP
c#stored-procedures
Bhavnish
Bhavnish

Reputation: 85

Error Converting DataType varchar to Numeric

In my stored procedure, I get data from three tables but got this error:

Error Converting Data Type varchar to Numeric.

I think problem is in :- 

'NULL' AS DATE (in 1st query of this SP)------(1)
CONVERT(VARCHAR, tbl_IPD.ipd_doa, 103) AS DATE (in 2nd query of this SP)-----(2)

My question is: how to convert (1) line to varchar

SELECT 
   tbl_CompanyProfile.Company_Name, tbl_CompanyProfile.Company_Address, 
   tbl_CompanyProfile.Company_ContactNo, tbl_CompanyProfile.Company_Email, 
   tbl_CompanyProfile.Company_Website, 
   'NULL' AS NAME, 'NULL' AS DATE,
   'NULL' AS AMOUNT, 'NULL' AS DEPARTMENT, 
   0 AS Age, @from AS StartDate, @to AS EndDate
FROM 
   tbl_CompanyProfile

UNION ALL

SELECT   
   '--', '--',
   '--', '--', '--', 
   tbl_IPD.ipd_pfname + ' ' + tbl_IPD.ipd_plname AS NAME, 
   CONVERT(VARCHAR, tbl_IPD.ipd_doa, 103) AS DATE, 
   ISNULL(SUM(tbl_fee.fee_amount), 0) AS AMOUNT, 
   tblDepartment.DeptName AS DEPARTMENT, tbl_IPD.ipd_age AS Age,  
   @from AS StartDate, @to AS EndDate
FROM            
   tbl_fee 
LEFT OUTER JOIN
   tbl_IPD ON tbl_fee.ipd_id = tbl_IPD.ipd_id 
LEFT OUTER JOIN
   tblDepartment ON tbl_IPD.ipd_dpt = tblDepartment.DeptId 
WHERE        
   (tbl_IPD.ipd_doa BETWEEN @from AND @to)
GROUP BY 
    tbl_IPD.ipd_pfname, tbl_IPD.ipd_plname, tbl_IPD.ipd_doa, tblDepartment.DeptName, tbl_IPD.ipd_age

Upvotes: 0

Views: 631

Answers (2)

D Stanley
D Stanley

Reputation: 152566

I think the problem is actually in the AMOUNT column - you are selecting NULL (character string) in the first query and trying to join it to a numeric result in the second query. If you want to use an actual NULL value in the union just take the apostrophes off of the NULL references.

Upvotes: 0

Claudio Redi
Claudio Redi

Reputation: 68400

If you're trying to return a NULL value you should remove quotes on 'NULL', otherwise you're returning a varchar and that's not what you're aprobably trying to do.

If my assumption is correct, this is how your query should looks like.

...NULL AS NAME, NULL AS DATE, NULL AS AMOUNT, NULL AS DEPARTMENT,...

Upvotes: 2

Related Questions