Solved

SQL - Best way to write this SP

Posted on 2016-08-01
9
36 Views
Last Modified: 2016-08-01
I'm looking for the best way to write this as a stored procedure.  

DECLARE @ContainerName VarChar(100)
DECLARE @ContainerID Int
DECLARE @CompanyID Int
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @ContainerName = ''
SET @ContainerID = ''
SET @CompanyID = ''
SET @StartDate = ''
SET @EndDate = ''


  
DELETE FROM [dbo].[AuditGas] WHERE [Audit] Like '%' + @ContainerName + '%' AND [CompanyID] = @CompanyID AND [DateStamp] BETWEEN @StartDate and @EndDate
  
Delete FROM [dbo].[ContainerEvents] WHERE [ContainerID] = @ContainerID AND [DateStamp] BETWEEN @StartDate and @EndDate

DELETE FROM [dbo].[ContainerNameplateCapacityChanges] WHERE [ContainerID] = @ContainerID AND [ChangeDate] BETWEEN @StartDate and @EndDate

DELETE FROM [dbo].[ContainerSpecs] WHERE [ContainerID] = @ContainerID AND [DateStamp] BETWEEN @StartDate and @EndDate

DELETE FROM [dbo].[CurrentGasWeight] WHERE [ContainerID] = @ContainerID AND [ChangeDate] BETWEEN @StartDate and @EndDate

DELETE FROM [dbo].[Emission] WHERE [ContainerID] = @ContainerID AND [DateStamp] BETWEEN @StartDate and @EndDate

DELETE FROM [dbo].[WarningEvents] WHERE [ContainerID] = @ContainerID AND [DateStamp] BETWEEN @StartDate and @EndDate

DELETE FROM [dbo].[WeighingEvents] WHERE [ContainerID] = @ContainerID AND [DateStamp] BETWEEN @StartDate and @EndDate

Open in new window

0
Comment
Question by:CAMPzxzxDeathzxzx
  • 5
  • 4
9 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41737762
Define 'best' by explaining to us what your requirements are, as 'best' is pretty subjective and we'd rather not guess.

If you mean parameterize the variables...
CREATE PROC your_proc (
	@ContainerName VarChar(100)  = '', 
	@ContainerID Int, 
	@CompanyID Int, 
	@StartDate DateTime, 
	@EndDate DateTime) 
AS 

-- Meaningful code comments go here. 

DELETE FROM dbo.AuditGas WHERE Audit Like '%' + @ContainerName + '%' AND CompanyID = @CompanyID AND DateStamp BETWEEN @StartDate and @EndDate
Delete FROM dbo.ContainerEvents WHERE ContainerID = @ContainerID AND DateStamp BETWEEN @StartDate and @EndDate
DELETE FROM dbo.ContainerNameplateCapacityChanges WHERE ContainerID = @ContainerID AND ChangeDate BETWEEN @StartDate and @EndDate
DELETE FROM dbo.ContainerSpecs WHERE ContainerID = @ContainerID AND DateStamp BETWEEN @StartDate and @EndDate
DELETE FROM dbo.CurrentGasWeight WHERE ContainerID = @ContainerID AND ChangeDate BETWEEN @StartDate and @EndDate
DELETE FROM dbo.Emission WHERE ContainerID = @ContainerID AND DateStamp BETWEEN @StartDate and @EndDate
DELETE FROM dbo.WarningEvents WHERE ContainerID = @ContainerID AND DateStamp BETWEEN @StartDate and @EndDate
DELETE FROM dbo.WeighingEvents WHERE ContainerID = @ContainerID AND DateStamp BETWEEN @StartDate and @EndDate

GO

Open in new window


Couple of observations..
  • You can't set a numeric or date data type to an empty string ''.  You can set it to NULL.  Explain in further detail what you're trying to do there.
  • The use of BETWEEN is not abundantly best practice, as that implies that anything that is one or the other would be returned in the WHERE, which may not be intended.
  • I eliminated the unnecessary square brackets.
0
 

Author Comment

by:CAMPzxzxDeathzxzx
ID: 41737827
I'll be passing the parameters and there will be no empty strings.

I'm looking for opinions to answer things like using between.

I thought somebody would recommend adding start / end to each delete.

Best = save me from making mistakes that experience has taught you:)

Bottom Line:  If you are a hard core SQL developer and you need to accomplish this within a single SP - How would you do it?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41737837
>I'll be passing the parameters and there will be no empty strings.
Ok, but your code SET all variables to empty strings '', and that's why I commented on that.

>I'm looking for opinions to answer things like using between.
Read SQL Expert Paul Maxwell's article Beware of Between.  In general it's a best practice to use this instead (change the names to meet your situation)

WHERE dtStart >= @some_start_date AND dtEnd < @some_end_date

Open in new window


>Best = save me from making mistakes that experience has taught you:)
Understand, but please also understand that this is not the most actionable question, as essentially it's a 'look at my code and tell me stuff', which means it's hard to tell when the question is completely answers.  Much better to ask specific questions.   Check out Top 10 Ways to Ask Better Questions, Number 10 for some added commentary.

>If you are a hard core SQL developer and you need to accomplish this within a single SP - How would you do it?
That was answered in my first comment, but without details on what exactly you need it's hard to state whether the code I provided will meet all requirements.
0
 

Author Comment

by:CAMPzxzxDeathzxzx
ID: 41737843
>If you are a hard core SQL developer and you need to accomplish this within a single SP - How would you do it?
 That was answered in my first comment, but without details on what exactly you need it's hard to state whether the code I provided will meet all requirements.

>>>> Your first answer did not recommend -
WHERE dtStart >= @some_start_date AND dtEnd < @some_end_date

Open in new window


I do appreciate your help.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:CAMPzxzxDeathzxzx
ID: 41737857
What do you think?
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[usp_ContainerRollbackBetweenDates]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_ContainerRollbackBetweenDates];
GO

CREATE PROCEDURE [dbo].[usp_ContainerRollbackBetweenDates]
(
	@ContainerName VarChar(100), 
	@ContainerID Int, 
	@CompanyID Int, 
	@StartDate DateTime, 
	@EndDate DateTime

)
AS
BEGIN

	DELETE FROM [dbo].[AuditGas] WHERE [Audit] Like '%' + @ContainerName + '%' AND [CompanyID] = @CompanyID AND [DateStamp] >= @StartDate AND [DateStamp] < @EndDate
  
	Delete FROM [dbo].[ContainerEvents] WHERE [ContainerID] = @ContainerID AND [DateStamp] >= @StartDate AND [DateStamp] < @EndDate

	DELETE FROM [dbo].[ContainerNameplateCapacityChanges] WHERE [ContainerID] = @ContainerID AND [ChangeDate] >= @StartDate AND [ChangeDate] < @EndDate

	DELETE FROM [dbo].[ContainerSpecs] WHERE [ContainerID] = @ContainerID AND [DateStamp] >= @StartDate AND [DateStamp] < @EndDate

	DELETE FROM [dbo].[CurrentGasWeight] WHERE [ContainerID] = @ContainerID AND [ChangeDate] >= @StartDate AND [ChangeDate] < @EndDate

	DELETE FROM [dbo].[Emission] WHERE [ContainerID] = @ContainerID AND [DateStamp] >= @StartDate AND [DateStamp] < @EndDate

	DELETE FROM [dbo].[WarningEvents] WHERE [ContainerID] = @ContainerID AND [DateStamp] >= @StartDate AND [DateStamp] < @EndDate

	DELETE FROM [dbo].[WeighingEvents] WHERE [ContainerID] = @ContainerID AND [DateStamp] >= @StartDate AND [DateStamp] < @EndDate

END
GO

Open in new window

0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41737865
Looks good.  Couple of thoughts.
  • Will StartDate-EndDate ever have a NULL value?  If yes, you'll want to consider how to handle that, but SETting them to a dummy value or maybe some IF.. custom logic.
  • Same with ContainerID and CustomerID.  Will there ever be a NULL passed, or maybe a value that is intended to be an '<ALL>'?
  • Are any of these tables related to other tables?  If yes you may have to change the order, or maybe add other tables.
0
 

Author Comment

by:CAMPzxzxDeathzxzx
ID: 41737887
Couple of thoughts... = All Good!  Thnaks
0
 

Author Closing Comment

by:CAMPzxzxDeathzxzx
ID: 41737888
Thanks
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41737890
Thanks for the grade.  Good luck with your SP.  -Jim
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Inner Join - Multiple Join Parameters 2 24
Can someone plz fix this..getting an error 3 19
Sql Count with Select Distinct 4 28
Update data using formula 22 23
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now