Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

create a stored procedure for delete rows

Posted on 2014-01-17
12
Medium Priority
?
232 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Assisted Solution

by:jogos
jogos earned 1000 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 1000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

636 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