using SUM in sp and result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

using SUM in sp and result

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-04-01 : 09:23:19

Here's my sp. It returns a value, but as an integer. I want the values like this 145.00 or 201.35

CREATE PROCEDURE dbo.CashUp

-- Add the parameters for the stored procedure here
@MOP nvarchar(50)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
RETURN (SELECT SUM(AmtPaid) from tblSales WHERE DT_MOP = @MOP)
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-01 : 09:30:53

@MOP nvarchar(50) should be decimal(12,2)

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-04-01 : 09:32:12

@MOP is not a figure it is nvarchar ie Cash, Cheque, BACs .....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-01 : 09:40:03

Ok.

Use

RETURN (SELECT cast(SUM(AmtPaid) as decimal(12,2)) from tblSales WHERE DT_MOP = @MOP)

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-04-01 : 09:44:56

It's still returning an integer....

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-04-01 : 10:30:38

Look up RETURN in BOL. Return ALWAYS returns an integer value. You need to SELECT your value.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-01 : 11:29:11

Use output parameter to return the value. Return value is typically used for returning status (ok, error code otherwise)

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources