[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

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
0
dba123
Asked:
dba123
  • 3
  • 3
1 Solution
 
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
 
LowfatspreadCommented:
select @totalspace = coalesce(@totalspace,0) - coalesce(@spaceused,0)
 
0
 
dba123Author Commented:
thanks, didn't think it was so simple as that.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now