Need help with stored procedure...

[nc297]

I need to get the total from two stored procedures. How could I add icnt + ncnt = tcnt to the below query or should I just sum icnt + ncnt and put the results into a separate table? Here's the stored procedure.

Truncate table iclaims
insert iClaims
Select Sort='1',Reg='NAT', Region='0', Area='00', Dist = '000', Doc = '000', convert(char,b.mnth_endt,1) as DowrDt, iCnt, tCnt, rpt='1'
From
(select mnth_endt, sum(zip_count) as iCnt
from [dbo].[iClaimsTemp]
where typ_of_actn='a' and abap_prc_cd NOT IN('y','s') and Bicnum IN('002','010') and inet_ind = '1'
--where INET_IND <> 'N' and (bicnum = '002' or bicnum = '202' or bicnum = '303' or bicnum = '304') and reg is not null and area is not null
Group by mnth_endt
)a
right join
(select mnth_endt, sum(zip_count) as nCnt
from [dbo].[iClaimsTemp]
Where typ_of_actn = 'a' and Bicnum = '002' and inet_ind = 'n' and abap_prc_cd NOT IN('y','s')
--where (bicnum = '002' or bicnum = '202' or bicnum = '303' or bicnum = '304') and reg is not null and area is not null
Group by mnth_endt
)b
on b.mnth_endt = a.mnth_endt

 

[markros]

You can return your totals from different stored procedures using OUTPUT parameters.

Then, in the main SP that calls these two SPs you simply take these totals (which are output parameters) and sum them.

PluralSight Learning Library

 

[nc297]

Should I put those outpput parameters into the same table as the other one's or just put it in it's own table?

 

[markros]

Please check BOL about how to create stored procedure and what OUTPUT parameters are.

The output parameters are scalar values, similar to INPUT parameters with the difference you can get the value back from SP.



PluralSight Learning Library

 

[nc297]

Thanks I know how to create a stored procedure but will look up output parameters on BOL to see what they are.

 

[nc297]

Okay I looked it up and came up with this:

alter function dbo.gettotals
(@icnt int, @tcnt int)
returns bigint
as begin
return
(select sum(icnt + tcnt) as total
from iclaims
where icnt = @icnt and tcnt=@tcnt)
end


select dbo.gettotals(total)

When I run the select statement I'm getting:

Invalid column name 'total' and An insufficient number of arguments were supplied for the procedure or function dbo.gettotals.

I want to sum(icnt + tcnt) and call that new column totals and have that go into the iclaims table. Both columns (icnt + tcnt) are in the iclaims table.


I got this to work but I love to learn new things and want to try to get this output parameter to work. Could someone assist me?

select sort, reg, region, area, dist, doc, dowrdt,rpt, sum(icnt + tcnt) as total
from iclaims
where reg='nat'
group by sort, reg, region, area, dist, doc, dowrdt, rpt
order by sort

 

[markros]

You're confusing FUNCTION and STORED PROCEDURE.

Stored Procedure can return values through SELECT, Return (although it only returns an integer value and usually used to return a status code) and it can also return multiple scalar values through OUTPUT parameters.

User Defined functions can be of two major types - scalar (that can return only a single value) or table-valued, that returns its result as a table.

In your initial message you were talking about stored procedures, so that's why I suggested to use output parameters to return extra information.

I think your current way of implementation is as good as it gets. You clearly don't need a scalar UDF or table-valued UDF here.

PluralSight Learning Library

 

[nc297]

Ok thanks so much! I want to add the total column into the iclaims table should I add the total column to the iclaims table and do an insert?

 

[markros]

If your IClaims table is a permanent working table, then I suggest to NOT add Totals column into it.

You can have two options:

1. Have a view that will calculate Total as a separate column

2. Have a computed column in a table (it's like a virtual column).

Both are OK options and should work for you.

If this table is sort of a temp table, then it's OK to add totals to it.

PluralSight Learning Library

 

[nc297]

The iclaims table is a permanent table so how could I set up a computed column for total?

 

[markros]

In the table designer in SSMS, add new column, go to Computed Column Specification, click on +, and in the formula put iCnt+tCnt (if this is your formula) and then save your table.

Then do a select statement and you will see this new field added.

PluralSight Learning Library

 

[nc297]

okay thanks I will try it in the morning.

 

[nc297]

Thanks Markros never knew I could do that! That worked nicely for me on only thing when I have a null in one of the columns I get a null for the computed column. Is there a way to change that?

 

[nc297]

Okay I added this in the computed column but it gave me 0 in that column

(isnull([icnt]+[ncnt],(0)))

How could I write this to give me the number that's there instead of 0?

 

[gmmastros]

When you add a null with anything, the result is null.

Looking at the inner most part: [icnt]+[ncnt]
If either one is null, that expression becomes null. To fix this problem, you need to convert both of them to 0 if they are null. Like this.

(IsNull([icnt], 0) + IsNull([ncnt], 0))

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nc297]

Thanks again! That's why I love this site I learn something new every time I come to it.

 

[nc297]

Okay I computed the column and now trying to add it to another table in which I've written a stored procedure to do that for me.

The table iClaimsYear has the computed column tCnt in it

CREATE TABLE [dbo].[iClaimsYear](
[Sort] [varchar](1) NOT NULL,
[Reg] [varchar](3) NOT NULL,
[Region] [varchar](1) NOT NULL,
[Area] [varchar](2) NOT NULL,
[Dist] [varchar](3) NULL,
[Doc] [varchar](3) NOT NULL,
[DowrDt] [smalldatetime] NULL,
[iCnt] [int] NULL,
[nCnt] [int] NULL,
[tCnt] AS (isnull([icnt],(0))+isnull([ncnt],(0))) PERSISTED,
[rpt] [varchar](1) NOT NULL
) ON [PRIMARY]


Where would I add the tcnt column to this stored procedure so that it can appear in the iclaimsYrPivot table.

Drop Table iClaimsYrPivot

DECLARE @pivot_cols NVARCHAR(MAX);
SELECT @pivot_cols =
STUFF((SELECT ',MAX(CASE WHEN year_month = ''' + year_month +
''' THEN perc END) AS [' + year_month + ']' +
',RANK() OVER(ORDER BY MAX(CASE WHEN year_month = '''

+
year_month +
''' THEN perc END)) AS [' + year_month + ' rank]' +
',SUM(CASE WHEN year_month = ''' + year_month +
''' THEN icnt END) AS [' + year_month + ' icnt]' +
',SUM(CASE WHEN year_month = ''' + year_month +
''' THEN ncnt END) AS [' + year_month + ' ncnt]'
FROM (SELECT DISTINCT RIGHT(CONVERT(VARCHAR(9),
CAST(dowrdt AS DATETIME), 6), 6),
CONVERT(CHAR(7), CAST(dowrdt AS DATETIME),
126)
FROM iclaimsYear) AS T(year_month, sort)
ORDER BY sort
FOR XML PATH('')
), 1, 1, '');

DECLARE @pivot_query NVARCHAR(MAX);
SET @pivot_query =
N'SELECT sort, reg, ' + @pivot_cols +
N' ,region, area, dist, doc, rpt ' +
N' INTO iClaimsYrPivot ' +
N'FROM (SELECT sort, reg, region, area, dist, doc, rpt, icnt, ncnt, tcnt, dowrdt, ' +
N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME),
6),
6) AS year_month, ' +
N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS decimal
(18,
1)) AS perc ' +
N'FROM iClaimsYear) AS F ' +
N'GROUP BY sort, reg, area, dist, doc, rpt, region ' +
N'ORDER BY sort;';


EXEC(@pivot_query);

 

[nc297]

I got this to work by adding the other column to my pivot columns but my percentage is off.

Is this the correct way to get percentage of two columns?

N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS decimal
(18,
1)) AS perc

Here's the full stored percedure
Drop Table iClaimsYrPivot

DECLARE @pivot_cols NVARCHAR(MAX);
SELECT @pivot_cols =
STUFF((SELECT ',MAX(CASE WHEN year_month = ''' + year_month +
''' THEN perc END) AS [' + year_month + ']' +
',RANK() OVER(ORDER BY MAX(CASE WHEN year_month = '''

+
year_month +
''' THEN perc END)) AS [' + year_month + ' rank]' +
',SUM(CASE WHEN year_month = ''' + year_month +
''' THEN icnt END) AS [' + year_month + ' icnt]' +
',SUM(CASE WHEN year_month = ''' + year_month +
''' THEN ncnt END) AS [' + year_month + ' ncnt]'
+
',SUM(CASE WHEN year_month = ''' + year_month +
''' THEN tcnt END) AS [' + year_month + ' tcnt]'
FROM (SELECT DISTINCT RIGHT(CONVERT(VARCHAR(9),
CAST(dowrdt AS DATETIME), 6), 6),
CONVERT(CHAR(7), CAST(dowrdt AS DATETIME),
126)
FROM iclaimsYear) AS T(year_month, sort)
ORDER BY sort
FOR XML PATH('')
), 1, 1, '');

DECLARE @pivot_query NVARCHAR(MAX);
SET @pivot_query =
N'SELECT sort, reg, ' + @pivot_cols +
N' ,region, area, dist, doc, rpt ' +
N' INTO iClaimsYrPivot ' +
N'FROM (SELECT sort, reg, region, area, dist, doc, rpt, icnt, ncnt, tcnt, dowrdt, ' +
N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME),
6),
6) AS year_month, ' +
N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS decimal
(18,
1)) AS perc ' +
N'FROM iClaimsYear) AS F ' +
N'GROUP BY sort, reg, area, dist, doc, rpt, region ' +
N'ORDER BY sort;';


EXEC(@pivot_query);