This gives me the result
This is giving me result like
Individually it gives me count as 1 but when executed inside stored procedure it give me 0.
SO how do I implement this inside the procedure? Please note some time there may not be any result set in the 'finished' block that is why I am using
Firstly COALESCE wont be affecting how many rows are returned, it will simply apply its effort on whatever rows there are.
I think you may need to review the value of @MID as that appears in the WHERE clause in the procedure but does not have any code to avoid it being NULL. If it is NULL then your result set could be empty. Try replacing
answered Jul 16 '12 at 08:32 AM
The advice from Jonathan Allen is excellent but I would restate it as if "@MID" paramater would be NULL, then the whole dynamic SQL will become NULL.... There should be a section to validate the NULL values for @MID and @Year.
Also, I do not see anything wrong with the execution if it is returning 0's for no results. But if it is the problem with a same parameter, then debug it by printing the dynamic sql. This is the first basic step to troubleshoot the dynamic sql.
answered Jul 16 '12 at 10:54 AM