Solved

create a stored procedure for delete rows

Posted on 2014-01-17
12
230 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
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

690 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