asked on

Stored Procedure Problem

Hi,

I have stored procedure that when I run it produces this error message:

Variable assignment is not allowed in a statement containing a top level UNION, INTERSECT or EXCEPT operator.

The only Union statement within the stored procedure is this:

      SELECT @li_item_id = child_id FROM work_references WHERE parent_id=@li_wo_id AND parent_type=140 AND child_type=1 AND subject_data ='Y'
      UNION
      SELECT @li_item_id = parent_id FROM work_references WHERE child_id=@li_wo_id AND child_type=140 AND parent_type=1 AND subject_data ='Y'

Can the same result be achieved by removing the union bit?

Thanks.

ASKER CERTIFIED SOLUTION

Saurabh Bhadauria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Louis01

To do it whithout a union:

SELECT @li_item_id = CASE WHEN parent_id=@li_wo_id 
                           AND parent_type=140 
                           AND child_type=1 
                          THEN child_id 
                          WHEN child_id=@li_wo_id 
                           AND child_type=140 
                           AND parent_type=1 
                          THEN parent_id 
                     END
  FROM work_references 
 WHERE subject_data ='Y'
   AND (   (    parent_id=@li_wo_id 
            AND parent_type=140 
            AND child_type=1 )
        OR (    child_id=@li_wo_id 
            AND child_type=140 
            AND parent_type=1 ))

Open in new window

Edit 1:
Personally though, I'd use the UNION like saurv suggested.

Jim Horn

>SELECT @li_item_id = child_id ...
>UNION
>SELECT @li_item_id = parent_id ...

What exactly is the point of assigning a value(s) from two different SELECT clauses to a scalar variable, joined by a UNION?

If @li_item_id is a table variable and not a scalar (i.e. single value) variable, please say so.

Jim Horn

Just a thought, explain for us in standard English what you're trying to pull off here.

Something like..
"Assign to variable @li_item_id the child_id if it exists, and if it doesn't assign the parent_Id"
"Populate a temp table with all child_id and parent_id values based on these queries"
"Heck, I have no idea what I'm doing here, and am hoping you guys can help"