No value returned from stored procedure

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)

No value returned from stored procedure

Author

Topic

fizgig
Starting Member

34 Posts

Posted - 2004-05-19 : 03:04:36

Hi,

Why doesn't this stored procedure return a value? When debugging the stored procedure @size has values larger than 0. Thank you for your help.

CREATE PROCEDURE getMailBoxSize
@id int
 AS

DECLARE @size int
DECLARE @send int
DECLARE @received int

SELECT @send = SUM(mailSize) FROM mailSend WHERE userId = @id
SELECT @received = SUM(mailSize) FROM mailReceived WHERE userId = @id


SELECT @size = CAST(COALESCE(@send, 0) +  COALESCE(@received,0) as int)

RETURN @size
GO

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-05-19 : 03:57:10

Looks fine to me. How are you capturing the return value, presumably using ADO at your front end? Try checking it in query analyzer first:


declare @retval int
exec @retval = getMailBoxSize 1202  --supply a real id here
print @retval

fizgig
Starting Member

34 Posts

Posted - 2004-05-19 : 04:04:14

Ah, thank you. I did use the query analyzer, but like this:


getMailBoxSize 1202

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-19 : 04:55:27

Not a good idea to use the return value for data, save it for error codes - better to use an output parameter.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources