Help with stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with stored procedure

Author

Topic

McBeef
Starting Member

14 Posts

Posted - 2012-09-05 : 12:15:51

I have put together a stored procedure that takes, calculates and re-formats some additional data and then finally uses the results to insert a record into a table. Once I get the SP working correctly, I will also want to have CrateID be an output. So here is my code so far:

ALTER PROCEDURE [dbo].[AddCrate] 
	@JobID INT, 
	@GrossWeight INT,
	@TareID INT,
	@NetWeight INT,
	@MachineID INT,
	@ShiftID INT,
	@InventoryID INT
AS
	DECLARE @BoxNumber VARCHAR(2)
	DECLARE @BuildDate DATE
	DECLARE @Julian VARCHAR(3)
	DECLARE @Year VARCHAR(2)
	DECLARE @CrateID VARCHAR(11)

	SET @BoxNumber = (SELECT qryBoxToday.Box+1 FROM qryBoxToday WHERE JobID = @JobID)
	SET @BuildDate = dateadd(day,datediff(day,0,getdate()),0)
	SET @Julian = dbo.PadChar('0', DATEDIFF(d,CAST((CAST(YEAR(@BuildDate) AS CHAR(4)) + '-01-01') AS
SMALLDATETIME),GETDATE()), 3)
	SET @Year = dbo.PadChar('0', ( YEAR( @BuildDate ) % 100 ), 2)
	SET @CrateID = @Julian + @Year + dbo.PadChar('0', @JobID, 4) + dbo.PadChar('0', @BoxNumber, 2)
	
	INSERT INTO Crates (CrateID, fkJobID, BoxNumber, BuildDate, GrossWeight, fkTareID, NetWeight, fkMachineID, fkShiftID, fkInventoryID) 
VALUES (@CrateID, @JobID, @BoxNumber,@BuildDate, @GrossWeight, @TareID, @NetWeight, @MachineID, @ShiftID, @InventoryID)

PadChar is a function I wrote for character padding (probably not relevant but here it is anyways).

ALTER FUNCTION [dbo].[PadChar] (@padchar char, @data VARCHAR(max), @length int)
RETURNS varchar(max)
AS
BEGIN
    RETURN replicate(@padchar, @length -LEN(@data)) + @data
END

Question #1: How can I get BoxNumber value assigned correctly?

When I run the SP from Visual Studio, BoxNumber never gets assigned a value. I have tried BoxNumber as an INT or VARCHAR with the same result. What is interesting is that the corresponding query always returns a numeric value of 1 or more.

SELECT qryBoxToday.Box FROM qryBoxToday WHERE JobID = 214

For assigning the BoxNumber in the SP I have also tried this with the same result:

SELECT @BoxNumber = qryBoxToday.Box+1 FROM qryBoxToday WHERE JobID = @JobID

Question #2: Best approach to CrateID?

The breakdown of a CrateID is as follows:
Characters 1 - 3: Julian Date
Characters 4 - 5: 2 digit year, zero padded
Characters 6 - 9: 4 digit Job ID, zero padded
Characters 10 - 11: 2 digit Box Number, zero padded

The column CrateID is a bigint. Do I need to convert it prior to the insertion? In the SP I have to do all this character padding on the 4 elements that make it up and then append them all together. So, correct me if I'm wrong, having it as string initially makes sense.

Now I now some people may take issue with the numbering scheme and will offer scraping that for something much simpler as the solution. I agree with that but the thing is that system this in already running with a lot of records, I'm simply moving the code to calculate the Crate from the VB client to a stored procedure.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-05 : 12:32:35

3 things:

1. You can accomplish the same formatting without using any string operations, simply by multiplying each portion by the correct power of 10 and adding those together.
2. Since you can derive the CrateID from BuildDate, BoxNumber and JobID, you don't really need it as a physical column in the Crates table. You can make it a computed column, or skip it entirely.
3. Having said that, what is the CrateID identifying? Where else is it used? (other tables?)

McBeef
Starting Member

14 Posts

Posted - 2012-09-05 : 13:15:40

quote:
Originally posted by robvolk

3 things:

1. You can accomplish the same formatting without using any string operations, simply by multiplying each portion by the correct power of 10 and adding those together.
2. Since you can derive the CrateID from BuildDate, BoxNumber and JobID, you don't really need it as a physical column in the Crates table. You can make it a computed column, or skip it entirely.
3. Having said that, what is the CrateID identifying? Where else is it used? (other tables?)

1. OK. Is this more efficient than the string and zero padding route? Lots of multiplication vs. lots of string operations?
2. See 3.
3. It identifies a unique identifier used to lookup crate records. I know, I know. If I could do it over again from scratch, I would Autonumber but this an existing system and I need to be able to support the numerous existing records. There is no foreign key relationship with CrateID with other tables, but all the same I would like to see what can be within the constraints of the current design.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-05 : 14:22:45

The math isn't that much:


ALTER PROCEDURE [dbo].[AddCrate] 
	@JobID INT, 
	@GrossWeight INT,
	@TareID INT,
	@NetWeight INT,
	@MachineID INT,
	@ShiftID INT,
	@InventoryID INT
AS
	DECLARE @BoxNumber INT, @BuildDate DATE, @CrateID BIGINT

	SET @BoxNumber = (SELECT Box+1 FROM qryBoxToday WHERE JobID = @JobID)
	SET @BuildDate = GETDATE() --dateadd(day,datediff(day,0,getdate()),0)
	SET @CrateID = ((DATEPART(DY,@BuildDate)-1) * CAST(100000000 AS BIGINT)) + 
	(YEAR(@BuildDate) % 100 * 1000000) + (@JobID * 100) + @BoxNumber
	
	INSERT INTO Crates (CrateID, fkJobID, BoxNumber, BuildDate, GrossWeight, fkTareID, NetWeight, fkMachineID, fkShiftID, fkInventoryID) 
	VALUES (@CrateID, @JobID, @BoxNumber,@BuildDate, @GrossWeight, @TareID, @NetWeight, @MachineID, @ShiftID, @InventoryID)

The nice thing is you no longer need a padding function.

One note, I used DATEPART to get the "Julian" date, but it returns a different value than what you did, so I subtracted 1. DATEPART(dy,'01/01/2012') would return 1, while your function would return 0. This would impact the # of digits of the CrateID if you use a bigint rather than varchar.

Here's how you can use a computed CrateID from the other columns:

ALTER TABLE Crates ADD CrateID AS ((DATEPART(DY,BuildDate)-1) * CAST(100000000 AS BIGINT)) + 
(YEAR(BuildDate) % 100 * 1000000) + (fkJobID * 100) + BoxNumber

You would then remove CrateID from the INSERT statement:

ALTER PROCEDURE [dbo].[AddCrate] 
	@JobID INT, 
	@GrossWeight INT,
	@TareID INT,
	@NetWeight INT,
	@MachineID INT,
	@ShiftID INT,
	@InventoryID INT
AS
	DECLARE @BoxNumber INT, @BuildDate DATE

	SET @BoxNumber = (SELECT Box+1 FROM qryBoxToday WHERE JobID = @JobID)
	SET @BuildDate = GETDATE() --dateadd(day,datediff(day,0,getdate()),0)
	
	INSERT INTO Crates (fkJobID, BoxNumber, BuildDate, GrossWeight, fkTareID, NetWeight, fkMachineID, fkShiftID, fkInventoryID) 
	VALUES (@JobID, @BoxNumber,@BuildDate, @GrossWeight, @TareID, @NetWeight, @MachineID, @ShiftID, @InventoryID)

McBeef
Starting Member

14 Posts

Posted - 2012-09-06 : 02:28:24

Worked great thank you for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources