Complex cursor SP and error handling

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

General SQL Server Forums

New to SQL Server Programming

Complex cursor SP and error handling

Author

Topic

nick_dkc
Starting Member

25 Posts

Posted - 2010-06-07 : 09:16:21

This forum has helped me to get some fantastic solutions to many problems I have faced as a novice and not very logical baby SQL programmer. But this cause problems sometimes because the solutions I have found don't always give me a 100% solution for my real-time problem! So here goes - this is a long one - and if you think it needs splitting up into pieces, please help me and tell me!

We ship pet animals all over the world.

CREATE TABLE [dbo].[petList]
(petID int NOT NULL
,custID int NOT NULL
,petName nvarchar(25) NULL)

INSERT INTO dbo.petList
VALUES (1, 100, 'Purr')
VALUES (2, 101, 'Stink')
VALUES (3, 102, 'Furry')
VALUES (4, 103, 'Hairy')
VALUES (5, 104, 'Fluffy')
VALUES (6, 105, 'Woof')
VALUES (7, 106, 'Bark')
VALUES (8, 107, 'Grrr')

When we ship them, they travel in special boxes, usually one box per pet.
(in this example I am showing the pet name to help the illustration - in reality we use a view - but the names are important because we have chosen to use them to identify sharing pets)

CREATE TABLE [dbo].[travelBoxes]
(exportID int NOT NULL
,p_petID int NOT NULL
,p_petName nvarchar(25) NULL
,sharingWith char (200) NULL
,boxID
,boxDimensions)

INSERT INTO dbo.exportBoxes
VALUES (5, 100, 'Purr', null, 100, 50x50x50)
VALUES (5, 101, 'Stink', null, 101, 60x60x60)
VALUES (5, 102, 'Furry', null, 102, 45x45x45)
VALUES (5, 103, 'Hairy', null, 103, 35x35x35)

As you will see, the default here is that p_petID = boxID, and each box has its own dimension

But sometimes more than one pet travels in the same box. In our client side application (A Singularity solution), the user simply types in a comma delimited string of pets into the 'sharingWith' field in a table and saves that row. We then use:
a. stored procedure to 'split' the comma delimited string and update the boxID for pets who are sharing to equal the boxID of the first (primary) pet.
b. a function to get the p_petID for a named pet.
b. a function to set a flag on boxes that are shared ('cos we need that somewhere else).
c. a trigger to set the dimensions for any pet sharing with another to NULL.

Visually this looks like this:

exportID p_petID p_petName sharingWith boxID dimensions
5 100 Purr Stink, Furry 100 50x50x50
5 101 Stink NULL 100 NULL
5 102 Furry NULL 100 NULL
5 103 Hairy NULL 103 35x35x35

PROBLEM - THE STORED PROCEDURE WORKS NEARLY OK :-), BUT DOES NOT (A) TEST THE DELIMITED NAMES AGAINST THE PETLIST TABLE AND (B) DOESN'T GIVE ANY ERROR RESULT BACK TO THE CLIENT APPLICATION.

I would like help in changing this SP so that (A) it CHECKS the result of delimiting the string against pet names in the petList table and (B) gives an error if it fails.

Here's the stored procedure to split the 'sharingWith' string.
------------------
ALTER PROCEDURE [dbo].[SplitDelimitedString](
@sInputList varchar(500) -- List of delimited pet names
, @Delimiter char(1) = ',' -- delimiter that separates pet names
, @boxID INT -- boxID of 'primary' pet
, @exportID INT
)

AS BEGIN

SET NOCOUNT ON

DECLARE @p_petName varchar(500), @p_petID INT, @ID int, @Ptname char

CREATE TABLE #tempPets (p_petName varchar(500), p_petID INT, boxID INT) -- Creates a temporary table

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0
BEGIN
SELECT
@p_petName=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList))))

IF LEN(@p_petName) > 0
INSERT INTO #tempPets SELECT @p_petName, @p_petID, @boxID

END

IF LEN(@sInputList) > 0
INSERT INTO #tempPets SELECT @sInputList, @p_petID, @boxID -- Puts the last item in

DECLARE pet_cursor CURSOR FOR
SELECT p_petName FROM #tempPets
ORDER BY p_petName
OPEN pet_cursor
FETCH Next FROM pet_cursor
INTO @PtName
Set @ID=0

WHILE @@FETCH_STATUS = 0
BEGIN
set @id=dbo.ufn_findpetidbyname(@ptname,@ExportID)
UPDATE TravelBoxes
SET boxID =@boxID where p_petID=@ID
FETCH NEXT FROM pet_cursor
INTO @PtName
END
CLOSE pet_cursor
DEALLOCATE pet_cursor

UPDATE TravelBoxes
SET boxID =@boxID where p_petID=@boxID

SELECT * FROM #tempPets
DROP TABLE #tempPets
RETURN
END
------------------

Here's the function that gets the p_petID of the primary (first) pet, to use as boxID for other 'sharing' pets.
------------------
CREATE FUNCTION [dbo].[uFn_findPetIDbyname](@Ptnm char,@ExpID int)
RETURNS int
AS
BEGIN
DECLARE @p_petName char, @p_petID int, @ID int
DECLARE pet_cursor CURSOR FOR
SELECT p_petName, p_petID FROM dbo.Travelboxes where ExportID=@expID

OPEN pet_cursor
FETCH NEXT FROM pet_cursor
INTO @p_petName, @p_petID

Set @ID=0
WHILE @@FETCH_STATUS = 0
BEGIN
if @ptnm=@p_petName
begin
set @ID=@p_petID
break
end
FETCH NEXT FROM pet_cursor
INTO @p_petName, @p_petid
END

CLOSE pet_cursor
DEALLOCATE pet_cursor

return @ID
END
------------------
And Here's the function that sets a flag if pets are sharing in another travel box
------------------
CREATE FUNCTION [dbo].[uFn_TravelBoxes_AfterShareFlag](@ptID int,@bxnum int,@ExpID int)
RETURNS bit
AS
BEGIN
DECLARE @boxID int, @petId int, @ExportID int, @flag bit
DECLARE box_cursor CURSOR FOR
SELECT boxID, petID ,ExportID FROM dbo.TravelBoxes
ORDER BY exportID, boxID, petID
OPEN box_cursor
FETCH NEXT FROM box_cursor
INTO @boxID, @petid ,@ExportID
Set @flag=0
WHILE @@FETCH_STATUS = 0
BEGIN
if @ptid=@petid
begin
FETCH NEXT FROM box_cursor
INTO @boxID, @petId, @ExportID
if @ExpID=@ExportID and @@FETCH_STATUS = 0
begin
if @bxnum=@boxID
begin
set @flag=1
break
end
end
end
FETCH NEXT FROM box_cursor
INTO @boxID, @petId,@ExportID
END

CLOSE box_cursor
DEALLOCATE box_cursor

return @flag
END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-07 : 11:04:38

Is there any chance you can change your schema at this point? If your actual tables are as you have described, then it appears you have a design issue.

But, in order to solve the issue you posted about... When you split the string into the temp table. Just do a LEFT OUTER JOIN to the PetList table and see if there any any unmatched (NULL) values. If there are, then raise an error.

nick_dkc
Starting Member

25 Posts

Posted - 2010-06-07 : 22:02:13

Hi lamprey, thanks for the feedback. The actual tables are a bit more complex than the 'post' examples I have used. Can you share what your design concerns are with me? Changing the schema isn't a problem if you suggest that there are serious flaws.

As for the LEFT OUTER JOIN to check against the petList - where/how do I do that?

Thanks
Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources