December 10, 2015 at 2:04 pm
I want a Case statement inside my IF EXISTS... Is my syntax correct?
ALTER Procedure dbo.usp_InsertNewClaims
(
@Client varchar(40),
@TableName varchar(50)
)
AS
IF EXISTS(
Select Case @Client
When 'Winco Foods' Then
SELECT *
FROM ClaimSummary_Winco CSW
Inner Join CTXExportedClaims CTX
ON CSW.CompanyCode + 'X' + CSW.ClmNbr = CTX.ClaimIdentifier
When 'SSI' Then
SELECT *
FROM ClaimSummary_Winco CSW
Inner Join CTXExportedClaims CTX
ON CSW.ClmNbr = CTX.ClaimIdentifier
End
)
BEGIN
--This means it exists, return and tell us
SELECT 'Already Loaded'
END
ELSE
BEGIN
INSERT INTO ClaimSummary
(ClmNbr, AuditNbr, PRGCode, AuditorID, ClmAmt, VndNbr, VndName, Year, CreateDt, SourceCode, ClaimCode, CompanyCode, DeptCode, BuyerCode, DealNbr, ClaimStatus, InvStatus, CancelAmt, BatchNbr)
SELECT ClmNbr, AuditNbr, PRGCode, AuditorID, ClmAmt, VndNbr, VndName, Year, CreateDt, SourceCode, ClaimCode, CompanyCode, DeptCode, BuyerCode, DealNbr, ClaimStatus, InvStatus, CancelAmt, BatchNbr
FROM ClaimSummary_Winco;
...
...
...
December 10, 2015 at 7:38 pm
No, the syntax is somewhat off. A CASE expression must evaluate to a single value.
For what you're doing, try something like this instead:
IF @Client = 'Winco Foods' AND EXISTS(
SELECT *
FROM ClaimSummary_Winco CSW
Inner Join CTXExportedClaims CTX
ON CSW.CompanyCode + 'X' + CSW.ClmNbr = CTX.ClaimIdentifier
)
OR @Client = 'SSI' AND EXISTS(
SELECT *
FROM ClaimSummary_Winco CSW
Inner Join CTXExportedClaims CTX
ON CSW.ClmNbr = CTX.ClaimIdentifier
)
BEGIN
--This means it exists, return and tell us
SELECT 'Already Loaded'
END
ELSE
BEGIN
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 10, 2015 at 10:05 pm
Thanks! this is what i needed.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply