Using If Statements in Stored Procedure
-
I'm trying to use an If Statement in the stored procedure below. Does anyone have an idea how I can get this to work? Right now I'm getting an incorrect syntax message. What am I doing wrong?
CREATE PROCEDURE sp_SrchNameDesc
(
@frmInput1 DateTime,
@frmInput2 DateTime
)
AS
SELECT
DataTable, CustomerBoxes.CustomerName, CustomerBoxes.CustType, Sum(ImageItems) AS Stubs, Sum(Correspondence) AS Corr, Sum(Unbankables) AS Unbank,
Sum(ExtractOnly) AS Extract, Sum(GoodChecks) AS Checks, Sum(PostalCards) AS PostalReturns,
Sum(Photocopies) AS Suspense, Sum(Lookups) AS LookupItems, Sum(Attempts) AS RAttempts,
Sum(Returns) AS ReturnItems, Sum(Correspondence) + Sum(Unbankables) AS Unprocessables, Sum(ImageItems) AS Image,
Sum(If(tblDataTableNew.CustType)=Wachovia,(ImageItems),0)) AS WviaImage
FROM
tblDataTableNew, CustomerBoxes
WHERE
CustomerID = DataTable AND VolDate >= @frmInput1
AND
VolDate <= @frmInput2 AND (ImageItems + ExtractOnly + Correspondence + Unbankables + Returns + Lookups) > 0
GROUP BY
DataTable,CustomerBoxes.CustomerName,CustomerBoxes.CustType
ORDER BY
CustomerBoxes.CustomerName;
GO
-
Use a CASE conditional statement instead (if is not allowed in the select statement), example:
CASE tbldatatablenew.custtype when 'xyz' then ImageItems else 0 end)
You can also do:
CASE
WHEN col1 = 'Y' then 2
WHEN col1 = 'X' and col2 = 20 then 3
else ....
end
-
Thanks for the response rsharma. But I'm really kind of new to SQLServer. How would I include the sum function using CASE in the Stored Procedure I show above? I'd appreciate it if you could help me out a bit.
-
Sum(CASE tblDataTableNew.CustType='Wachovia' THEN ImageItems ELSE 0 END) AS WviaImage
_/_/_/ paramind _/_/_/ -
paramind, that is just what I was looking for. Thank you for responding and helping this poor soul.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply