x

Coalesce FETCHING 0 ROWS inside stored procedure but fetches result as a query

 ALTER PROCEDURE [dbo].[Status_yearly]
 (@MID NVARCHAR(max))
 AS
 BEGIN
 
 DECLARE @Years nvarchar(max)
 SELECT @Years = Stuff((SELECT DISTINCT ',[' 
                                        + Cast(Year(u.StartDate) AS 
                                        NVARCHAR(4 
                                               )) 
                                        + ']' 
                        FROM   [products] u 
                        FOR xml path('')), 1, 1, '') 
 DECLARE @date nvarchar(max)
 SET @date =CONVERT(VARCHAR(10),GETDATE(),105)
 -- SELECT @date
 DECLARE @SQL nvarchar(max)
 SELECT @SQL = '
 select
     *
 from (
     select
       ''Product'' AS Status,''total_product'' as [YEAR], 
       productname, year(u.StartDate) as [y]
     from products u  WHERE 
     MID ='''+@MID+''' 
     AND u.Flag <> 0 
 
   ) Data
 PIVOT (
   COUNT(productname)
   FOR [y]
   IN (
     ' + @Years + '
   )
 ) PivotTable
 
 UNION ALL 
 select * from ( select ''Finished'' AS Finished, ''In Time'' as [YEAR], Coalesce(COUNT(productsname),0) AS productsname, 
 Coalesce(COUNT(year(u.StartDate)),0) as [y] 
 from products u
  WHERE u.AStartdate IS NOT NULL
  AND u.AStartdate = u.pStartdate 
 AND MID ='''+@MID+''
 
  ) Data
 PIVOT (
   COUNT(productsname)
   FOR [y]
   IN (
     ' + @Years + '
   )
 ) PivotTable
 
 '
 EXECUTE (@SQL)
 END

This gives me the result

 Status     Year            2012
 Product   total_product     4
 FINISHED  In Time           0




 

 select ''Finished'' AS Finished,
       ''In Time'' as [YEAR], 
      Coalesce(COUNT(productsname),0) AS productname, Coalesce(COUNT(year(u.StartDate)),0) as [y]
     from products u  WHERE  u.AStartdate IS NOT NULL 
     AND u.AStartdate = u.pStartdate
     AND MID ='''+@MID+''' 

This is giving me result like

 Finished   YEAR     productname  Y
 FINISHED  In Time        1       1

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

 Coalesce(COUNT(productsname),0) AS productname, Coalesce(COUNT(year(u.StartDate)),0) 

please suggest..

more ▼

asked Jul 16, 2012 at 05:15 AM in Default

avatar image

dharmendra1
20 4 4 7

I am not sure what is the problem here? If there would be no results sets then count should be zero as given? Can you please post some test data, desired output and the wrong output according to the data?

Jul 16, 2012 at 05:39 AM Usman Butt

And you do not have to use COALESCE as the output would already be shown as zero with the COUNT.

Jul 16, 2012 at 05:42 AM Usman Butt

If I am not using COALESCE then I am not getting that rows even there is a empty set. Like FINISHED In Time 0 will not come if I do not add COALESCE function

Jul 16, 2012 at 06:31 AM dharmendra1

OK I have revisited your code it has more working than the problem you described like the years string, PIVOT etc. So there might be some problem in there. Without having some test data and with such least information about the exact problem, it is pretty difficult to help.

Jul 16, 2012 at 07:08 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

 AND MID ='''+@MID+''

with

 AND MID ='''+ISNULL(@MID,'')+''
more ▼

answered Jul 16, 2012 at 08:32 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jul 16, 2012 at 10:54 AM

avatar image

Sacred Jewel
1.7k 3 7 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2091

asked: Jul 16, 2012 at 05:15 AM

Seen: 1240 times

Last Updated: Jul 16, 2012 at 10:54 AM

Copyright 2016 Redgate Software. Privacy Policy