CODEWITHSUNDEEP
sqlsql-server
Zia Zee
Zia Zee

Reputation: 17

SQL Server. dynamic query error while using aggregate function

I am little bit new to SQL. I am trying to run below shown dynamic query

SET @SQL ='
select Distinct count(*) TotalCount,
    sum(case when ap.FormStatus = ''open'' then 1 else 0 end) AS Totalopenstatus,
    sum(case when ap.FormStatus = ''Pending'' then 1 else 0 end) AS TotalPendingstatus,
    sum(case when ap.FormStatus = ''Awaiting L1 Review'' then 1 else 0 end) AS TotalAssociates,
    sum(case when ap.FormStatus = ''Awaiting L2 Review'' then 1 else 0 end) AS TotalL1reviews,
    sum(case when ap.FormStatus = ''Awaiting Delivery Head'' then 1 else 0 end) AS TotalL2reviews
    from [dbo].[Appraisals] ap
    LEFT join [dbo].[FinalReviewClosures] fi ON ap.FinalReviewClosure_Id = fi.id
    join [dbo].[employees] emp ON fi.Employee_Id = emp.id
where fi.FinancialYear ='+@FinancialYear +'  AND ap.Quarter = '+@Quarter+' '

but I get this error:

Msg 245, Level 16, State 1, Procedure sp_GetReviewStatus, Line 51 [Batch Start Line 11]
Conversion failed when converting the nvarchar value

Code:

select Distinct count(*) TotalCount,
    sum(case when ap.FormStatus = 'open' then 1 else 0 end) AS Totalopenstatus,
    sum(case when ap.FormStatus = 'Pending' then 1 else 0 end) AS TotalPendingstatus,
    sum(case when ap.FormStatus = 'Awaiting L1 Review' then 1 else 0 end) AS TotalAssociates,
    sum(case when ap.FormStatus = 'Awaiting L2 Review' then 1 else 0 end) AS TotalL1reviews,
    sum(case when ap.FormStatus = 'Awaiting Delivery Head' then 1 else 0 end) AS TotalL2reviews
    from [dbo].[Appraisals] ap
    LEFT join [dbo].[FinalReviewClosures] fi ON ap.FinalReviewClosure_Id = fi.id
    join [dbo].[employees] emp ON fi.Employee_Id = emp.id
where fi.FinancialYear =2020-2021  AND ap.Quarter = Q3  AND emp.office = ' to data type int.

Please help me fix this problem.

Upvotes: 0

Views: 75

Answers (2)

Thom A
Thom A

Reputation: 95829

The reason what you have is failing is as has been mentioned. You are injecting values instead of parametrising and these values are creating invalid syntax in your "dynamic" query. (Read on to see why I say "dynamic".)

fi.FinancialYear ='+@FinancialYear +' becomes fi.FinancialYear =2020-2021, which is effectively fi.FinancialYear = -1 (what year is -1?). This will cause an error if fi.FinancialYear isn't able to be converted implicitly to an int.

ap.Quarter = '+@Quarter becomes ap.Quarter = Q3 and unless you have a column called Q3 then you're going to get an invalid column error.

If you are using dynamic SQL then never inject unsanitised values; quote your object names using QUOTENAME and parametrise your parameters (which you can do with sys.sp_executesql).

What you have, however, has no need to be a "dynamic" query; there is nothing dynamic about it. Just use a non-dynamic parametrised query and the query will work fine:

SELECT --DISTINCT --Not needed, as the query will only return 1 row.
                  --Even if you did have a GROUP BY the DISTINCT isn't needed,
                  --as the GROUP BY will already put the data in DISTINCT sets.
       COUNT(*) AS TotalCount,
       SUM(CASE WHEN ap.FormStatus = 'open' THEN 1 ELSE 0 END) AS Totalopenstatus,
       SUM(CASE WHEN ap.FormStatus = 'Pending' THEN 1 ELSE 0 END) AS TotalPendingstatus,
       SUM(CASE WHEN ap.FormStatus = 'Awaiting L1 Review' THEN 1 ELSE 0 END) AS TotalAssociates,
       SUM(CASE WHEN ap.FormStatus = 'Awaiting L2 Review' THEN 1 ELSE 0 END) AS TotalL1reviews,
       SUM(CASE WHEN ap.FormStatus = 'Awaiting Delivery Head' THEN 1 ELSE 0 END) AS TotalL2reviews
FROM [dbo].[Appraisals] ap
     LEFT JOIN [dbo].[FinalReviewClosures] fi ON ap.FinalReviewClosure_Id = fi.id
     JOIN [dbo].[employees] emp ON fi.Employee_Id = emp.id
WHERE fi.FinancialYear = @FinancialYear --I assume fi.FinancialYear and @FinancialYear have the same data type?
  AND ap.Quarter = @Quarter; --I assume ap.Quarter and @Quarter have the same data type?

Upvotes: 3

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Where clause should be

where fi.FinancialYear ='2020-2021'  AND ap.Quarter = 'Q3'  AND emp.office = ' to data type int.'

instead of

where fi.FinancialYear =2020-2021  AND ap.Quarter = Q3  AND emp.office = ' to data type int.

Please also let me know the data type of @financialYear and @Quarter

Upvotes: 0

Related Questions