Stored Procedure: How to handle empty resultset

I want to take the second select statement and do something like this but not sure of the syntax and approach in my sp:

If no results returned from select statment Then
@spaceused = 0

-----------------------------------------------------------------
ALTER           PROCEDURE sp_Get_DiskSpaceList_Request
     @ResourceID          int
AS

DECLARE @totalspace int,
      @spaceused  int

SET NOCOUNT ON

-- ### Get Total Hard Disk Space for Current Resource
SELECT  @totalspace = s.DiskSize
FROM    tbl_RServer_PC rp INNER JOIN tbl_DiskSize s ON rp.DiskSizeID = s.DiskSizeID
WHERE   s.DiskSizeID = rp.DiskSizeID AND rp.ResourceID = @ResourceID

-- ### Get DiskSpace used by each user
SELECT  @spaceused = s.DiskSize
FROM    tbl_Resource_Employee he INNER JOIN tbl_DiskSize s ON he.DiskSizeID = s.DiskSizeID
WHERE       he.ResourceID=24
LVL 1
dba123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

adwisemanCommented:
--Preset the variable with the desired results if the query returns no records.
Set @spaceused = 0

-- ### Get DiskSpace used by each user
SELECT  @spaceused = s.DiskSize
FROM    tbl_Resource_Employee he INNER JOIN tbl_DiskSize s ON he.DiskSizeID = s.DiskSizeID
WHERE      he.ResourceID=24



--This does not cover the cases when the query returns a NULL Value, you may want to add

IF @spaceused IS NULL SET @spaceused = 0

--After the query
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
select @totalspace = coalesce(@totalspace,0) - coalesce(@spaceused,0)
 
0
dba123Author Commented:
thanks, didn't think it was so simple as that.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dba123Author Commented:
shoot, Lowfatspread, didnt' see your post till after...sorry.

can you tell me though what coalesce does?
0
adwisemanCommented:
coalesce(@totalspace,0)

Returns the first non null item in a list,  In this case, your variable is the first item, and the number 0 is the second.

You could replace my line of code

IF @spaceused IS NULL SET @spaceused = 0

With

 SET coalesce(@spaceused, 0)

And you would get the same outcome.
0
adwisemanCommented:
sorry, replace it with
 SET @spaceused = coalesce(@spaceused, 0)
0
dba123Author Commented:
thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.