Solved

create a stored procedure for delete rows

Posted on 2014-01-17
12
229 Views
Last Modified: 2014-02-06
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
0
Comment
Question by:bibi92
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 15

Expert Comment

by:tim_cs
ID: 39788807
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
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39788823
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
 

Author Comment

by:bibi92
ID: 39788900
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 25

Assisted Solution

by:jogos
jogos earned 250 total points
ID: 39789138
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
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 250 total points
ID: 39789250
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
 
LVL 25

Expert Comment

by:jogos
ID: 39789900
The is null in the delete is necessary. In the exists-part it can be left out.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39790087
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
 
LVL 25

Expert Comment

by:jogos
ID: 39790330
You are right
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39792640
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
 
LVL 25

Expert Comment

by:jogos
ID: 39793499
Thx Angelp1ay. It is often overseen the split. But I can live with that.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39800135
@SouthMod - Move 250pts to ID: 39789138 as Assist, leaving current answer with the other 250.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question