Taking over someone elses' code and need help

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)

Taking over someone elses' code and need help

Author

Topic

risho
Starting Member

12 Posts

Posted - 2012-09-18 : 12:43:06

The sproc returns the following string looks like this:

ALTER procedure [dbo].[sp_Location_Get_Children]
(@pintParentIdent as int)
as
begin
SELECT *,dbo.udf_Location_Get_Hierarchy_String(ident) as HierarchyString
FROM location WHERE parent_ident = @pintParentIdent
end
---------------------------------------------

Returnd string:

1000 S. Main Street/Building 2012/Roon100/2A7
----------------------------------------------

The data in the table looks like this:


ident  parent_ident description         is_address is_street
-----  ------------ ------------------- ---------- ---------
1      1            92084-0000          1          0
2      1            CA                  1          0
3      2            San Diego           1          0
4      3            1000 S. Main Street 0          1
5      4            Building 2012       0          0
6      5            R100                0          0
7      6            1A1                 0          0 
8      6            2A7                 0          0

The function that builds the string looks like this:


ALTER FUNCTION [dbo].[udf_Location_Get_Hierarchy_String]
( @locIdent int ) 
RETURNS varchar(100)
AS

begin
	declare @locHierarchyString varchar(100),
			@locParentIdent int,
			@locParentDesc varchar(100),
			@locIsAddress bit

	select @locHierarchyString = description, 
		@locParentIdent = parent_ident, @locIsAddress = is_address from Location WHERE ident = @locIdent

	while((@locParentIdent is not null) and (@locParentIdent != @locIdent))
	begin
		select @locParentDesc = description,  @locIsAddress = is_address from Location WHERE ident = @locParentIdent
		if(@locIsAddress = 0)
		begin		
			set @locHierarchyString = @locParentDesc + ' / ' + @locHierarchyString		
		end

		set @locIdent = @locParentIdent
		select @locParentIdent = parent_ident from Location WHERE ident = @locParentIdent
		
	end
		
	RETURN @locHierarchyString
end

I'm having a real tough time following what the function does. I see that it builds the string with a loop but nothing beyond that. I need to modify the function so that it only builds room number and cubicle code like this: R100/2A7.

Can someone help me out? Or perhaps suggest a different approach without modifying or deleting data from the table.

Thanks,

EJM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 13:09:01

is there a bit field or level field to identify room/cubicle?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

risho
Starting Member

12 Posts

Posted - 2012-09-18 : 13:40:29

quote:
Originally posted by visakh16

is there a bit field or level field to identify room/cubicle?

No, threre isn't but I suppose I could add a column as it sholdn't hurt anyting.

EJM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 13:43:36

unless you've any other fields to identify room/cubicle its difficult as they're all stored in same field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

risho
Starting Member

12 Posts

Posted - 2012-09-18 : 13:52:11

quote:
Originally posted by visakh16

unless you've any other fields to identify room/cubicle its difficult as they're all stored in same field

Okay, let's suppose I've added a filed is_cube with default value of 1 for each row that is cubicle. Can you take it from here?

EJM

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-18 : 14:23:57

If you add that new column, the following modified function should work:

ALTER FUNCTION [dbo].[udf_Location_Get_Hierarchy_String](@locIdent INT) 
RETURNS VARCHAR(100) AS
BEGIN
	DECLARE @path VARCHAR(100);
	WITH CTE AS (SELECT *, CAST(Description AS VARCHAR(MAX)) AS PATH FROM Location 
	WHERE ident=@locIdent
	UNION ALL
	SELECT P.*, P.Description + ' / ' + A.Path
	FROM CTE A
	INNER JOIN Location P ON P.ident=A.Parent_ident
	WHERE P.is_cube=1)
	SELECT @path=PATH FROM CTE 
	WHERE PATH<>description
	RETURN @path
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 14:27:20

then you can use that field in concatenation logic to avoid concatenating other values

ie


..
select @locParentDesc = description,  @locIsAddress = is_address,@Is_cube = is_cube from Location WHERE ident = @locParentIdent
		if(@locIsAddress = 0)
		begin		
			set @locHierarchyString = @locParentDesc + case when @Is_cube =1 then ' / ' + @locHierarchyString else '' end		
		end
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

risho
Starting Member

12 Posts

Posted - 2012-09-18 : 17:41:46

quote:

Originally posted by visakh16

then you can use that field in concatenation logic to avoid concatenating other values

ie


..
select @locParentDesc = description,  @locIsAddress = is_address,@Is_cube = is_cube from Location WHERE ident = @locParentIdent
		if(@locIsAddress = 0)
		begin		
			set @locHierarchyString = @locParentDesc + case when @Is_cube =1 then ' / ' + @locHierarchyString else '' end		
		end
...

Thaks for all your help, but I must say that it is not woking. The resulting string is NULL.

EJM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-19 : 12:12:24

it will work if you use it properly as suggested. show your code with how table data is (particularly is_cube field)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources