Problem with declaring variables in SP

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

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with declaring variables in SP

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2008-09-26 : 11:09:47

Here is my SP -

CREATE PROCEDURE ASP_AccountBalance

@ClientID int

Declare @BB Double
Declare @PM Double
Declare @INV Double
Declare @Bal Double

exec @BB = SELECT abs(sum(tblclients.beginningbalance))
FROM tblclients WHERE tblclients.clientid=@Clientid

exec @PM = select abs(sum(tblpayments.amtpaid))
from tblpayments where tblpayments.clientno=@clientid

exec @INV = select abs(sum(tblinvoicehdr.totaldue))
from tblinvoicehdr where tblinvoicehdr.clientid=@Clientid and tblinvoicehdr.status <> "Void"

set @Bal = @BB+@INV-@PM

Return @Bal

I am new to SP so I know something is run thus I get errors all over where I declare my variables. Could someone take a quick peak and see whats wrong and if I am slo doing anything wrong with this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:13:06

Double is not a valid datatype in sql server.
use float instead.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:18:23

Also, stored procedure can only return integers as return codes.
Either make use of an output parameter like this

CREATE PROCEDURE ASP_AccountBalance
(
	@clientID INT,
	@balance FLOAT OUT
)
AS

SET NOCOUNT ON

DECLARE @BB FLOAT,
	@PM FLOAT,
	@INV FLOAT

SELECT	@BB = ABS(SUM(beginningBalance))
FROM	tblClients
WHERE	clientID = @clientID

SELECT	@PM = ABS(SUM(amtPaid))
FROM	tblPayments
WHERE	clientNo = @clientID

SELECT	@INV = ABS(SUM(totalDue))
FROM	tblInvoiceHdr
WHERE	clientID = @clientID
	AND [status] <> 'Void'

SET	@balance = @BB + @INV - @PM

Or better use a function like this

CREATE FUNCTION ASP_AccountBalance
(
	@clientID INT
)
RETURNS FLOAT
AS
BEGIN
	DECLARE @BB FLOAT,
		@PM FLOAT,
		@INV FLOAT

	SELECT	@BB = ABS(SUM(beginningBalance))
	FROM	tblClients
	WHERE	clientID = @clientID

	SELECT	@PM = ABS(SUM(amtPaid))
	FROM	tblPayments
	WHERE	clientNo = @clientID

	SELECT	@INV = ABS(SUM(totalDue))
	FROM	tblInvoiceHdr
	WHERE	clientID = @clientID
		AND [status] <> 'Void'

	RETURN	@BB + @INV - @PM
END

E 12°55'05.63"
N 56°04'39.26"

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2008-09-26 : 11:21:03

I am still getting a lot of errors even after changing double to float.

I changed it up to this -

CREATE PROCEDURE ASP_AccountBalance

@ClientID int

as
Declare @BB float;
Declare @PM float;
Declare @INV float;
Declare @Bal float;

@BB = SELECT abs(sum(tblclients.beginningbalance))
FROM tblclients WHERE tblclients.clientid=@Clientid

@PM = select abs(sum(tblpayments.amtpaid))
from tblpayments where tblpayments.clientno=@clientid

@INV = select abs(sum(tblinvoicehdr.totaldue))
from tblinvoicehdr where tblinvoicehdr.clientid=@Clientid and tblinvoicehdr.status <> "Void"

set @Bal = @BB+@INV-@PM

Return @Bal

go

I still get errors when I try to run my 3 select statments. I get error near @BB and @PM and @INV about syntax

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:24:17

Oh... My... God!
Why don't you LOOK at the suggestions I have posted?
I have given you both a working stored procedure and a working function.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:26:12

This is the working procedure!
You can't return a DOUBLE (float) as a return code from a stored procedure.
You will need to either use a output parameter like this

CREATE PROCEDURE ASP_AccountBalance
(
	@clientID INT,
	@balance FLOAT OUT
)
AS

SET NOCOUNT ON

DECLARE @BB FLOAT,
	@PM FLOAT,
	@INV FLOAT

SELECT	@BB = ABS(SUM(beginningBalance))
FROM	tblClients
WHERE	clientID = @clientID

SELECT	@PM = ABS(SUM(amtPaid))
FROM	tblPayments
WHERE	clientNo = @clientID

SELECT	@INV = ABS(SUM(totalDue))
FROM	tblInvoiceHdr
WHERE	clientID = @clientID
	AND [status] <> 'Void'

SET	@balance = @BB + @INV - @PM

or return the balance as a resultset like this

CREATE PROCEDURE ASP_AccountBalance
(
	@clientID INT
)
AS

SET NOCOUNT ON

DECLARE @BB FLOAT,
	@PM FLOAT,
	@INV FLOAT

SELECT	@BB = ABS(SUM(beginningBalance))
FROM	tblClients
WHERE	clientID = @clientID

SELECT	@PM = ABS(SUM(amtPaid))
FROM	tblPayments
WHERE	clientNo = @clientID

SELECT	@INV = ABS(SUM(totalDue))
FROM	tblInvoiceHdr
WHERE	clientID = @clientID
	AND [status] <> 'Void'

SELECT	@BB + @INV - @PM AS balance

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:26:36

You best option will be to use a function like this

CREATE FUNCTION ASP_AccountBalance
(
	@clientID INT
)
RETURNS FLOAT
AS
BEGIN
	DECLARE @BB FLOAT,
		@PM FLOAT,
		@INV FLOAT

	SELECT	@BB = ABS(SUM(beginningBalance))
	FROM	tblClients
	WHERE	clientID = @clientID

	SELECT	@PM = ABS(SUM(amtPaid))
	FROM	tblPayments
	WHERE	clientNo = @clientID

	SELECT	@INV = ABS(SUM(totalDue))
	FROM	tblInvoiceHdr
	WHERE	clientID = @clientID
		AND [status] <> 'Void'

	RETURN	@BB + @INV - @PM
END

E 12°55'05.63"
N 56°04'39.26"

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2008-09-26 : 11:50:38

quote:
Originally posted by Peso

Oh... My... God!
Why don't you LOOK at the suggestions I have posted?
I have given you both a working stored procedure and a working function.

E 12°55'05.63"
N 56°04'39.26"

I saw this after. My apologies.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources