Two questions. Stored proc not working and conceptual question.

[bminaeff]

Hi All,
I have two questions, but that are somewhat related. First one is more important.

I have to write a stored procedure that is overall pretty simple, but I am just not sure the best way to do it. I originally wrote one and it seemed to work, but I figured there was a better and/or more efficent way to write it. I have three tables, "Sort_Table", "Archive_Table", and "ErrorCodes". ErrorCodes simply has two columns that relate an error number to some text, for instance 1 = success, 2 = not found, 3 = invalid assignment, 4 = multiple results. I get a number called UCC from a barcode and query the Sort_Table to return a lane. I would like the procedure to return back the lane as long as certain conditions are met and move my record from the "Sort_Table" table to the "Archive_Table" table. For now, copying is fine, but eventually I would like the "Sort_Table" table to delete that record. I think my possible conditions are :
1: No records returned, and output @LaneNum = 9, @Error_Code = 2.
2: Multiple records returned, output @LaneNum = 9, @Error_Code = 4.
3: 1 record returned AND @LaneNum > 9, output @LaneNum = 9, @Error_Code = 3
4: 1 record returned AND @LaneNum <=9, output @LaneNum = Sort_Table.LaneNum, @Error_code = 1

I initally used a few if statements and checked the @@RowCount, but I figured that using If Exists would allow the procedure to move onto condition 1 quicker if it happened, is this correct? Now I have this and it does not work. The multiple record condition returns back the last result in the table. Why is this? I was also considering using a Case statement. Which of these 3 methods would be the quickest?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Sort_Carton_R1]
	-- Add the parameters for the stored procedure here
	(@UCC [nvarchar] (20),
	 @LaneNum [int] OUTPUT)
		
	AS
BEGIN
DECLARE @LoadTime nvarchar(12)
DECLARE @LoadDate nvarchar(10)
DECLARE @SortTime nvarchar(12)
DECLARE @SortDate nvarchar(10)
DECLARE @Error_Code int 
DECLARE @Error_Text nvarchar(500)
DECLARE @LoadFileName nvarchar(50)
SET @SortTime = convert(nvarchar(12),GetDate(),114)
SET @SortDate = convert(nvarchar(10),GetDate(),1)
SET @LaneNum = '9'
SET @Error_Code = '2'
SET @LoadFileName = 'File Not Found'
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF EXISTS(SELECT * FROM Sort_Table WHERE Ucc = @UCC) BEGIN
		SELECT @LaneNum = LaneNum FROM Sort_Table WHERE Ucc = @UCC --Not needed?
		IF @@ROWCOUNT > 1 BEGIN
			SELECT @Error_Code = '4', @Error_Text = Error_Text FROM ErrorCodes WHERE Error_Code = '4'
			INSERT INTO Archive_Table 
			([Ucc], [LaneNum], [LoadTime], [LoadDate], [LoadFileName], [SortLane], [SortTime], [SortDate], [Error_Code], [Error_Text])
			VALUES
			(@UCC, @LaneNum, @SortTime, @SortDate, @LoadFileName, @LaneNum, @SortTime, @SortDate, @Error_Code, @Error_Text)
		END
		ELSE BEGIN
			IF @LaneNum > '9' BEGIN
				SELECT @Error_Code = '3', @Error_Text = Error_Text FROM ErrorCodes WHERE Error_Code = '3'
				SELECT @LoadTime = LoadTime, @LoadDate = LoadDate, @LoadFileName = LoadFileName, @LaneNum = '9' FROM Sort_Table WHERE UCC = @UCC
				INSERT INTO Archive_Table 
					([Ucc], [LaneNum], [LoadTime], [LoadDate], [LoadFileName], [SortLane], [SortTime], [SortDate], [Error_Code], [Error_Text])
				VALUES
					(@UCC, @LaneNum, @SortTime, @SortDate, @LoadFileName, @LaneNum, @SortTime, @SortDate, @Error_Code, @Error_Text)
			END
			ELSE BEGIN
				SELECT @Error_Code = '1', @Error_Text = Error_Text FROM ErrorCodes WHERE Error_Code = '1'
				SELECT @LaneNum = LaneNum, @LoadTime = LoadTime, @LoadDate = LoadDate, @LoadFileName = LoadFileName FROM Sort_Table WHERE UCC = @UCC
				INSERT INTO Archive_Table 
					([Ucc],[LaneNum], [LoadTime], [LoadDate], [LoadFileName], [SortLane], [SortTime], [SortDate], [Error_Code], [Error_Text])
				VALUES
					(@UCC, @LaneNum, @SortTime, @SortDate, @LoadFileName, @LaneNum, @SortTime, @SortDate, @Error_Code, @Error_Text)
			END
		END
	END
	ELSE BEGIN
		-- No Records Found
				SELECT @Error_Code = '2', @Error_Text = Error_Text FROM ErrorCodes WHERE Error_Code = '2'
				SELECT @LoadTime = LoadTime, @LoadDate = LoadDate, @LoadFileName = LoadFileName FROM Sort_Table WHERE UCC = @UCC
				INSERT INTO Archive_Table 
					([Ucc],[LaneNum], [LoadTime], [LoadDate], [LoadFileName], [SortLane], [SortTime], [SortDate], [Error_Code], [Error_Text])
				VALUES
					(@UCC, @LaneNum, @SortTime, @SortDate, @LoadFileName, @LaneNum, @SortTime, @SortDate, @Error_Code, @Error_Text)
			
	END
END

My second question is about speed of getting a return value. Again, what method (case vs. if exists) would be quicker in this method. Also, is there a way to return back a value and then move data around, or is this so minimal I shouldn't worry about it? The reason I ask is because I get these barcodes from boxes that are on conveyors and I have to return a lane back before a certain physical point.

Thanks for the help,
Bill M .

 

[bminaeff]

Nevermind. I forgot to set the lane assignment under the multiple record condition. Anyway, my other question still stands, but the code seems to work.