dba123
asked on
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_Reque
@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
If no results returned from select statment Then
@spaceused = 0
--------------------------
ALTER PROCEDURE sp_Get_DiskSpaceList_Reque
@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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Lowfatspread
select @totalspace = coalesce(@totalspace,0) - coalesce(@spaceused,0)
ASKER
thanks, didn't think it was so simple as that.
ASKER
shoot, Lowfatspread, didnt' see your post till after...sorry.
can you tell me though what coalesce does?
can you tell me though what coalesce does?
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.
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.
sorry, replace it with
SET @spaceused = coalesce(@spaceused, 0)
SET @spaceused = coalesce(@spaceused, 0)
ASKER
thank you