create a stored procedure for delete rows

Hello,

I try to create a stored procedure for delete rows with the following condition :

The procedure must remove all lines with as livSlipNumber = 0 or "" or "null" if there is only the same line ( but not equal livSlipNumber 0, "" or nul)

Herewith the table :

CREATE TABLE [dbo].[salesorder](
      [livSlipNumber] [nvarchar](20) NOT NULL,
      [SalesOrderNumber] [numeric](20, 0) NOT NULL,
      
) ON [PRIMARY]

GO

Thanks

Regards
bibi92Asked:
Who is Participating?
 
Angelp1ayCommented:
Re: IS NULL
I was meaning to comment on that. Since bibi specified not null on the columns and put it in quotes I assumed that it was a bizarre string value in the data.

If you mean actual nulls I think you can skip that since it's not possible in your columns.

Re: Query
I wrote the delete as a join instead of a correlated sub-query because AFAIK the sub-query path can get you into performance troubles.

Edit: ...but actually from what I've read since it appears I'm wrong :) What I was talking about is true for IN but it looks like EXISTS is the best option:
http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Updated!
I've just realised I only wrote the query not how to make it a stored procedure.

I believe the updated code should thus be the below (which is almost identical to jogos' except I think he had a couple of minor syntax errors and I'm not bothering to check the nulls since they're impossible):
CREATE PROCEDURE deleteBadData
AS 
    SELECT * FROM salesorder s1
    -- DELETE FROM salesorder s1 
    WHERE (s1.livSlipNumber = 0 OR s1.livSlipNumber = "")
    AND EXISTS (SELECT 1 FROM salesorder s2
                        WHERE s2.SalesOrderNumber = s1.SalesOrderNumber 
                        AND s2.livSlipNumber <> 0 
                        AND s2.livSlipNumber <> "")
GO

Open in new window

...again with the select version first for safety :)

Personally I prefer the JOIN syntax... I think EXISTS looks ugly and is harder to read.
0
 
tim_csCommented:
I'm not really sure what you're asking for.  Can you provide a data example of the data in the table and what the results would be after the proc runs?
0
 
Angelp1ayCommented:
Do you mean it should delete the rows only if there is another record with the same SalesOrderNumber which has non-null values?

SELECT * FROM salesorder s1
-- DELETE FROM salesorder s1 
INNER JOIN salesorder s2 
    ON s1.SalesOrderNumber = s2.SalesOrderNumber
    AND (s1.livSlipNumber = 0 OR s1.livSlipNumber = "" OR s1.livSlipNumber = "null")
    AND (s2.livSlipNumber <> 0 AND s2.livSlipNumber <> "" AND s2.livSlipNumber <> "null")

Open in new window


Does this produce the list of rows you want deleted?
Try running the select first (with the delete left commented) to check and if you're happy then swap the comment to the first line.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bibi92Author Commented:
Exactly I mean it should delete the rows only if there is another record with the same SalesOrderNumber which has non-null values.

Thanks
0
 
jogosCommented:
First the select-version (good habit as shown by Angelplay)
CREATE TABLE [dbo].[salesorder](
      [livSlipNumber] [nvarchar](20) NOT NULL,
      [SalesOrderNumber] [numeric](20, 0) NOT NULL,
      
) 
as 
BEGIN
 SELECT * FROM salesorder 
-- DELETE FROM salesorder 
where ( salesorder is null 
          or  salesorder.livSlipNumber = 0 
          or salesorder .livSlipNumber = "" 
          )
         AND  exists ( select * FROM salesorder s2 
                              WHERE s2.SalesOrderNumber = salesorder .SalesOrderNumber
                              AND ( s2.livSlipNumber is not null 
                                       AND s2.livSlipNumber <> 0 
                                       AND s2.livSlipNumber <> "" )
                              )                         
END
GO

Open in new window


Attention for the = "null" in the AngelPlay-query, that won't be what you want. And don't repacle it with = null without knowing what you are doing.
More on the ANSI_NULLS see http://technet.microsoft.com/en-us/library/ms188048.aspx
0
 
jogosCommented:
The is null in the delete is necessary. In the exists-part it can be left out.
0
 
Angelp1ayCommented:
The column has a not null constraint so it's impossible for there to be any nulls.
Why do you say it is necessary?
0
 
jogosCommented:
You are right
0
 
Angelp1ayCommented:
Thanks bibi.

I hope you don't mind but I asked the admin if this could be made a 250-250 split with jogos since we got to the final answer by bouncing off each other. Sharing is caring ;)

Have a great day both! :)
0
 
jogosCommented:
Thx Angelp1ay. It is often overseen the split. But I can live with that.
0
 
Angelp1ayCommented:
@SouthMod - Move 250pts to ID: 39789138 as Assist, leaving current answer with the other 250.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.