asked on

Another way of doing this SQL

Please see the screenshot. This is how I delete rows from the table but I'm wondering if there's a better way of doing this. This isn't hard but I'm drawing blank on this

This is what I have. This is SQL 2014. @test is sometimes NULL and sometimes has a value

declare @test int
set @test = 15439 --**** this is sometimes NULL

--select *  from AllCommsalesTest where invoicetoaccountid  is null

if (@test is null)
  begin
     delete from AllCommsalesTest where  InvoiceToAccountId is not null
  end 
  else
   begin
     select @test
    delete from AllCommsalesTest where  InvoiceToAccountId is null
   end

Open in new window

Éric Moreau

not sure of what you are trying to do. Do you want to delete rows where InvoiceToAccountId = @Test and if @Test is null, you want to delete all rows having InvoiceToAccountId = NULL?

Sean Stuber

You can do it all in a single sql statement.
It's more compact but I don't know that I'd call this "better"

delete from AllCommsalesTest
where (@test is not null and InvoiceToAccountId is null)
or
(@test is null and InvoiceToAccountId is not null)

Camillia

ASKER

If I pass in @test = null....I want to keep the rows that are null in invoiceaccountid

if I pass in @test = 1234...I want to keep rows that are 1234 in invoicetoaccountid

Vitor Montalvão

Yes, that's the best way. Have it all in a single statement can give you a worst performance.
I just don't know why do you need the select @test when is not null.

Camillia

ASKER

>>I just don't know why do you need the select @test when is not null

I was testing.

So, mine is ok?

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Éric Moreau

membership