Solved

create a stored procedure for delete rows

Posted on 2014-01-17
12
227 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 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