stored proc needs only one - 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 2000 Forums

SQL Server Development (2000)

stored proc needs only one

Author

Topic

sbrown999
Starting Member

5 Posts

Posted - 2006-01-30 : 23:00:35

I need to figure out a way to return a single result set for the following proc. Currently it returns a result set for the select on the cursor and one for everytime it calls spGetCommandUnitTopParent and one for the last select. Now I know that is what it is suppose to do. What I don't know is how to get around that. HELP!

CREATE PROCEDURE spGetAssignedEquipment (@personnel_id INT)
AS
-- turn no count on
SET NOCOUNT ON

--declare variables needed
DECLARE @current_command_id INT
DECLARE @equip_id INT
DECLARE @top INT

BEGIN
--get a cursor since its possible for equip from different units to be assign to a soldier
DECLARE personnel_equip CURSOR FOR
SELECT equipment_id, command_unit_id_assigned
FROM equipment
WHERE personnel_assigned_id = @personnel_id

--create a temp table to keep the top level units
CREATE TABLE #equip_command_units(equipment_id INT, command_unit_id INT)

--fill the temp table
OPEN personnel_equip

FETCH NEXT FROM personnel_equip
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM personnel_equip
INTO @equip_id, @current_command_id

EXECUTE @top = spGetCommandUnitTopParent @current_command_id

INSERT #equip_command_units SELECT @equip_id, @top
END

CLOSE personnel_equip
DEALLOCATE personnel_equip

END

--do the select
SELECT e.*, ue.max_rounds_fired
FROM equipment e, unit_equipment ue, #equip_command_units c
WHERE personnel_assigned_id = @personnel_id
AND e.equipment_definition_id = ue.equipment_definition_id
AND e.equipment_id = c.equipment_id
AND ue.command_unit_id = c.command_unit_id

GO

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-30 : 23:08:20

Hi sbrown,
I think spGetCommandUnitTopParent is returning a result set which is causing multiple results. check spGetCommandUnitTopParent.

sbrown999
Starting Member

5 Posts

Posted - 2006-01-30 : 23:22:24

It returns a return code, technically.

So now here is the weird thing. I have another proc that calls the same spGetCommandUnitTopParent. This is deployed at another developer's db that i gave him a couple of days ago. It return a single result set with the correct data for him. For me, 2 result sets.

?????????

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-30 : 23:27:46

Is there any difference in both the SPs? It would be better if you could post the Sp.

sbrown999
Starting Member

5 Posts

Posted - 2006-01-30 : 23:30:20

the one that works as deployed, but not on my local db.

CREATE PROCEDURE spGetEquipmentByBarCode (@Bar_Code varchar(40))
AS
-- turn no count on
SET NOCOUNT ON

--declare var. @level keeps track of the current level in the hierarchy
DECLARE @level int
DECLARE @current_command_id int

--create a temp table to keep things striaght
CREATE TABLE #stack (item char(20), level int)

--create a temp table to keep the return values
CREATE TABLE #command_unit_children(command_unit_id int)

--get the equip's unit id
SET @current_command_id = (SELECT command_unit_id_assigned FROM equipment WHERE Bar_Code = @Bar_Code)

--get the unit's top parent
DECLARE @top int
EXEC @top = spGetCommandUnitTopParent @current_command_id

--prime temp table with the command_unit_id that was passed in
INSERT INTO #stack VALUES (@current_command_id, 1)

--set @level at 1 so we can move down the chain
SELECT @level = 1

--keep going unitl we are at the bottom
WHILE @level > 0
BEGIN
--check to see if there is anything in the temp table at the givin level
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
--get the item from the temp table
SELECT @current_command_id = item
FROM #stack
WHERE level = @level

--insert into the ooutput table
INSERT #command_unit_children SELECT @current_command_id

--remove from temp table
DELETE FROM #stack
WHERE level = @level
AND item = @current_command_id

--get the next unit in the chain
INSERT #stack
SELECT command_unit_id, @level + 1
FROM command_unit
WHERE command_parent_id = @current_command_id

-- increment the level to the next
IF @@ROWCOUNT > 0
SELECT @level = @level + 1

END
ELSE
SELECT @level = @level - 1
END -- WHILE

SELECT e.*, ue.max_rounds_fired
FROM equipment e, unit_equipment ue
WHERE e.equipment_definition_id = ue.equipment_definition_id
AND e.Bar_Code = @Bar_Code
AND ue.command_unit_id = @top

GO

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-30 : 23:37:47

I was talking about posting spGetCommandUnitTopParent sp you might have misunderstoode me.. as nothing else in the sp can return the resultset except the last select statement.

sbrown999
Starting Member

5 Posts

Posted - 2006-01-30 : 23:44:51

sorry, here it is. and now that i look at it, that should the a "return @command_unit_id". hmmmmm.

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'spGetCommandUnitTopParent' AND type = 'P')
DROP PROCEDURE spGetCommandUnitTopParent
GO

CREATE PROCEDURE spGetCommandUnitTopParent (@current_command_id int)
AS
--turn no count on
SET NOCOUNT ON

--declare var. @level keeps track of the current level in the hierarchy
DECLARE @level int

--create a temp table to keep things striaght
CREATE TABLE #stack (item char(20), level int)

--create a temp table to keep the return values
CREATE TABLE #command_unit_children(command_unit_id int)

--set @level at 1 so we can move down the chain
SELECT @level = command_parent_id FROM command_unit
WHERE command_unit_id = @current_command_id

--prime temp table with the command_unit_id that was passed in
INSERT INTO #stack VALUES (@current_command_id, @level)

--keep going unitl we are at the bottom
WHILE @level IS NOT NULL
BEGIN
--check to see if there is anything in the temp table at the givin level
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
--get the item from the temp table
SELECT @current_command_id = item
FROM #stack
WHERE level = @level

--insert into the ooutput table
INSERT #command_unit_children SELECT @current_command_id

--remove from temp table
DELETE FROM #stack
WHERE level = @level
AND item = @current_command_id

SELECT @level = command_Parent_id
FROM command_unit
WHERE command_unit_id = @current_command_id

--get the next unit in the chain
INSERT #stack
SELECT command_Parent_id, @level
FROM command_unit
WHERE command_unit_id = @current_command_id

END
END -- WHILE

SELECT @current_command_id AS the_unit

GO

sbrown999
Starting Member

5 Posts

Posted - 2006-01-30 : 23:54:42

yup, that needed to be a return.

So that solves the deployed proc working, but the original proc still returns 2 sets. One from the select for the cursor(which, btw, if anyone has an idea on how to get rid of that I'm all ears) and one for that select on at the bottom of the proc.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-31 : 00:10:48

Hi ,
you should set @current_command_id as an output parameter to the SP spGetCommandUnitTopParent
and remove SELECT @current_command_id AS the_unit statement

while executing the Sp in other stored procedures

DECLARE @top int
EXEC spGetCommandUnitTopParent @current_command_id = @top output

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-31 : 00:26:31

Otherwise post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources