Returning Value from SQL 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

General SQL Server Forums

New to SQL Server Programming

Returning Value from SQL Stored Procedure

Author

Topic

gp_330
Starting Member

6 Posts

Posted - 2005-09-29 : 11:15:31

Hi Guys, I have a series of count statements in a Stored Procedure that return some values. What I want to do is take all of the values that have been returned by the Count statements, add them up and return the value to my .Net Code, but for some reason whenever I try I carry on getting a returned value of 0.

I have checked that the statements are in fact returning values other than 0, and I was wondering if there is something that I am doing wrong in the code below:

<code>
CREATE PROCEDURE [dbo].[UCOutstanding]
@userid int
AS
DECLARE @Num as Int
DECLARE @Num2 as Int
DECLARE @Num3 as Int
DECLARE @Num4 as Int

SET @Num = (SELECT count(*) FROM images, albums, memorial WHERE (images.active=0 AND images.albumid=albums.id AND albums.memorialid=memorial.id AND memorial.userid=@userid))
SET @Num2 = (SELECT count(*) FROM downloads, memorial WHERE (downloads.active=0 AND downloads.memorialid=memorial.id AND memorial.userid=@userid))
SET @Num3 = (SELECT count(*) FROM images, comments, albums, memorial WHERE (comments.active=0 AND comments.imageid=images.id AND images.albumid=albums.id AND albums.memorialid=memorial.id AND memorial.userid=@userid))
SET @Num4 = (SELECT count(*) FROM memorial, story WHERE (story.memorialID=memorial.id and memorial.userid=@userid))

DECLARE @Total as Int
SET @Total = @Num + @Num2 + @Num3 + @Num4

Return @Total
GO
</code>

Thanks for the Help
GP

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 12:03:49

Are any of them coming up NULL perhaps?

And reason not to do


SELECT @Total = (SELECT COUNT(*) FROM images ...)
              + (SELECT COUNT(*) FROM downloads ...)
              + (SELECT COUNT(*) FROM comments ...)
              + (SELECT COUNT(*) FROM story ...)

RETURN @Total

If there is a problem with NULL then you might need to do:

SELECT @Total = COALESCE((SELECT COUNT(*) FROM images ...), 0)

Kristen

gp_330
Starting Member

6 Posts

Posted - 2005-09-29 : 13:20:36

Dear Kristen,

Thanks for the reply. I didn't think that the NULL results would be a problem because I was using a Count(*) and not attempting to return any results, so even if no results were returned, the value would still be 0.

But otherwise I have checked all of the individual strings and there are definitely numbers other than 0 being returned, so I still don't know what the problem could be.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-29 : 13:38:16

I don't think the problem is sp code, I don't like old join syntax however. :)

I think problem is the way you are trying to read return value by your .net code.

I guess next code displays correct total:

declare @Total int

exec @Total = dbo.UCOutstanding @userID = <some user id>

select @Total

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 14:00:47

"I didn't think that the NULL results would be a problem because ..."

You're right, sorry. I was thinking of SUMming a column that contained NULLs. Getting near the end of the week!

Kristen

gp_330
Starting Member

6 Posts

Posted - 2005-09-30 : 07:45:02

Hi guys,

I found out that the problem was coming in on the ASP.net side of things, sorry to waste everyones time.

Thanks for all the help otherwise.

Thanks
GP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources