Select variables in a cursor

[wangotango]

I am having a problem getting the syntax right on this (if this is even legal in 12.5).

I am creating a stored procedure and I am inside of a cursor, looping on a temp table. I need to set two variables (@part_id and @ass_id) with each pass of the loop, and I am attempting to do so as follows:

SELECT @part_id, @ass_id =
SELECT part_id, ass_id
FROM partner
WHERE part_action_id = @part_action_id

All the variables have been declared. The variable @part_action_id is the variable that the cursor is fetching into. I am unable to compile the proc because of a syntax error near the keyword 'SELECT'.

@part_id and @ass_id are used for an insert further down inside of the cursor. Any suggestions on this? Any way to do this without (more) temp tables?

Thanks!

 

[sybaseguru]

Send the full code please mate.

 

[wangotango]

Here is a somewhat sanitized version of the full stored procedure. I hope it's sound enough for you to get an idea of what I'm trying to do.

CREATE PROCEDURE docs

AS

IF @@trancount = 0
SET CHAINED OFF

-- $Header$

-- Create temp table
CREATE TABLE #temp_rad (
act_code char(3) NOT NULL,
system_code char(6) NOT NULL,
part_action_id numeric(10) NOT NULL,
item_detail_id int NOT NULL,
item_code int NOT NULL,
item_smtxt varchar(255) NULL
)


-- Insert records to be updated into temp table
INSERT INTO #temp_rad
(act_code,system_code,part_action_id,item_detail_id,item_code,item_smtxt)
SELECT *
FROM rad_detail
WHERE act_code = 'RAA'
AND item_smtxt LIKE '%X%zip%'


-- Update the rows in the temp table
UPDATE #temp_rad
SET act_code = 'RBB'


BEGIN TRANSACTION

-- Delete the rows from rad_detail that are going to be replaced
DELETE
FROM rad_detail
WHERE part_action_id IN (
SELECT part_action_id
FROM #temp_rad
)
AND act_code = 'RAA'
AND item_smtxt LIKE '%X%zip%'


-- Create a cursor from the remaining rows
DECLARE doc_cursor CURSOR
FOR SELECT part_action_id
FROM #temp_rad


DECLARE @part_action_id numeric(10),
@date datetime,
@part_id numeric(10),
@ass_id numeric(9)

SELECT @date = getdate()

OPEN doc_cursor

FETCH doc_cursor INTO @part_action_id

WHILE @@sqlstatus <> 2
BEGIN

---------------------------------------------------------------------------------------------
-- THIS IS WHERE THE ERROR IS BEING THROWN - I need some info correspondant to this id from another table
---------------------------------------------------------------------------------------------
SELECT @part_id, @ass_id =
SELECT part_id, ass_id
FROM partner
WHERE part_action_id = @part_action_id


-- This is where I want to use the selected values - they will change each time through
-- This is abbreviated to show the idea
INSERT INTO partner
(...part_id,ass_id...)
VALUES(... @part_id, @ass_id...)


-- There will be some more processing here

-- Fetch next result set
FETCH doc_cursor INTO @part_action_id

END

IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN 0
END

GO

 

[wangotango]

I got the answer. This is what I was looking to do:

SELECT @part_id = part_id, @ass_id = ass_id
FROM partner
WHERE part_action_id = @part_action_id

Thanks!

 

[sybaseguru]

Yes I saw that error I was just coming back. Anyway glad that you sorted it out.