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

dharmendra1 gravatar image

dharmendra1
20 4 4 5

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

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(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

Sacred Jewel gravatar image

Sacred Jewel
1.7k 2 4 5

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1850

asked: Jul 16, 2012 at 05:15 AM

Seen: 1047 times

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