Adding values in row using Stored Procedure

Question

Hi guys

I'm very green to SQL. I'm trying to write a stored procedure that performs a select statement of the RequestID column and the total of the disk size for that row. ie the values on RequestAdditionalDisk1Size + RequestAdditionalDisk2Size + RequestAdditionalDisk3Size where the Requester equals a certain value.

I can perform the select statement fine on the individual values however I have no idea how to add the values of the Disk sizes together and present that back in the select statement.

 

So far the code looks like but is giving me an error around the line performing a SUM.

-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spMyRequests] 
	-- Add the parameters for the stored procedure here
	(
	@requesterid nvarchar(50)
	
	)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	-- SET NOCOUNT ON;
	DECLARE @totalDiskSpace INT
	SET @totalDiskSpace = (SUM(RequestDetails.RequestAdditionalDisk1Size, RequestDetails.RequestAdditionalDisk2Size, RequestDetails.RequestAdditionalDisk3Size))
    -- Insert statements for procedure here
	SELECT RequestID, RequestvCPUCount, @totalDiskSpace FROM dbo.RequestDetails WHERE Requester=@requesterid
END

Sorry I'm really not sure how to even go about doing this. Any help would be appreciated.

Cheers
Brady

Answer 1

Try

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	-- SET NOCOUNT ON;
	
	SELECT RequestID, RequestvCPUCount, 
coalesce(.RequestAdditionalDisk1Size,0) + 
coalesce(RequestAdditionalDisk2Size,0) +
coalesce(RequestAdditionalDisk3Size,0)
as totalDiskSpace 
FROM dbo.RequestDetails WHERE Requester=@requesterid
END

Also make sure that your parameter type and width matches the column's type and width.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 2

Hi Latheesh

That's it! Thanks very much for you help.

Cheers
Brady