I have a stored procedure that selects invoices based on the date range
delete from BillingCurrent
insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),
GETDATE(), 112))
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent
delete from Billing30
insert into Billing30(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing3
Now, I need to check to see if the row exists in Billing30, if it exists in Billing 30 then I don't want it to insert into BillingCurrent.
You can use something along the lines of the code listed below.
Chris
IF EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)
BEGIN
--Insert the SQL that you want to execute if a Billing30 record is found
END
ELSE
BEGIN
--Insert the SQL that you want to execute if a Billing30 record is not found
END
|||
Ok I understand that and it's very helpful but,
for
IF EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)
BEGIN
--Insert the SQL that you want to execute if a Billing30 record is found
I want it to do Nothing if the row exists but I don't want it to exit because I need to do this for 5 tables
END
|||
There's nothing to stop you using multiple IF statements or even nesting them if you desire, see below - note that I've reversed the logic.
Chris
IF NOT EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)
BEGIN
--Insert the SQL that you want to execute if a Billing30 record is not found
END
IF NOT EXISTS (SELECT 1 FROM Table2 WHERE <insert criteria>)
BEGIN
--Insert the SQL that you want to execute if a Table2 record is not found
END
IF NOT EXISTS (SELECT 1 FROM Table3 WHERE <insert criteria>)
etc....
|||
ok so I can nest all of the if not exists and then after those just
if exists
end
to make it do nothing if the row already exists?
|||
In my previous example none of the code within the BEGIN END blocks will execute if at least one row meeting the relevant criteria exists in each of the tables that you are checking. There's no need to add any additional code to make SQL Server do nothing - if a condition fails then the code within the associated BEGIN END block will not be executed, it's as simple as that. If all of the conditions fail then the batch will complete without executing any of the code within any of the BEGIN END blocks.
It isn't clear from the description of your scenario whether you will need to use nested or multiple IF statements so I can't help any further in that respect without more info.
Chris
|||
Ok, Heres my SP
This prints out (because I use a relation from the billing tables to the InvoiceDetails Table) A Billing statement for each customer, problem is: if a customer has an invoice this month and last month then if prints out two invoices. I'm trying to get it to check each table first billing120 then billing90 then billing60..... So the customer row only gets inserted once.
delete from Billing120
insert into Billing120(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112)Between CONVERT(varchar(15),dateadd (d,-150,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -215, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing120
delete from Billing90
IF NOT EXISTS (SELECT CustomerID FROM Billing120)--WHERE <insert criteria>)
BEGIN
insert into Billing90(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-120,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -90, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing90
END
delete from Billing60
IF NOT EXISTS (SELECT CustomerID FROM Billing90)
BEGIN
insert into Billing60(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-90,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -60, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing60
End
delete from Billing30
IF NOT EXISTS (SELECT CustomerID FROM Billing90)
BEGIN
insert into Billing30(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing30
END
delete from BillingCurrent
IF NOT EXISTS (SELECT CustomerID FROM Billing90)
BEGIN
insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),
GETDATE(), 112))
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent
END
RETURN
|||
Maybe you should try a different approach then, see below. This approach allows you to analyze the contents of the tables before performing any INSERTs etc...
Chris
DECLARE @.Billing120Exists BIT
DECLARE @.Billing90Exists BIT
DECLARE @.Billing60Exists BIT
DECLARE @.Billing30Exists BIT
SET @.Billing120Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing120) THEN 1 ELSE 0 END
SET @.Billing90Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing90) THEN 1 ELSE 0 END
SET @.Billing60Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing60) THEN 1 ELSE 0 END
SET @.Billing30Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing30) THEN 1 ELSE 0 END
--Insert logic here that examines the values of the @.BillingExists variables and performs the appropriate actions.
--If you want you can declare additional variables to indicate whether or not rows have subsequently been inserted into one of the tables.
|||
Actually I ended up doing it this way,
delete from Billing120
insert into Billing120(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112)Between CONVERT(varchar(15),dateadd (d,-150,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -215, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing120
delete from Billing90
BEGIN
insert into Billing90(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip
FROM Invoices INNER JOIN
Customers A ON Invoices.CustomerID = A.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-120,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -90, GETDATE()), 112)
And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing90
END
delete from Billing60
BEGIN
insert into Billing60(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip
FROM Invoices INNER JOIN
Customers A ON Invoices.CustomerID = A.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-90,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -60, GETDATE()), 112)
And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)
And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing60
End
delete from Billing30
BEGIN
insert into Billing30(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip
FROM Invoices INNER JOIN
Customers A ON Invoices.CustomerID = A.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)
And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)
And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)
And not exists (select 1 from billing60 D where D.CustomerID = A.CustomerId)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing30
END
delete from BillingCurrent
BEGIN
insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip
FROM Invoices INNER JOIN
Customers A ON Invoices.CustomerID = A.CustomerID
WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),
GETDATE(), 112))
And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)
And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)
And not exists (select 1 from billing60 D where D.CustomerID = A.CustomerId)
And not exists (select 1 from billing30 e where E.CustomerID = A.CustomerId)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent
END
RETURN
Thanks for the Help!
