Solved

Stored Procedure Problem

Posted on 2013-11-07
4
229 Views
Last Modified: 2013-11-22
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.
0
Comment
Question by:andyw27
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
Saurabh Bhadauria earned 500 total points
ID: 39629952
Try it like this..

select @li_item_id = Child_id  from 
(SELECT  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 parent_id FROM work_references WHERE child_id=@li_wo_id AND child_type=140 AND parent_type=1 AND subject_data ='Y'
) x 

Open in new window


Thanks,
saurabh
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39629977
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.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39630116
>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.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39630290
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"
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What's wrong with this T-SQL Foreign Key? 7 46
convert null in sql server 12 34
[SQL server / powershell] bulk delete table from CSV 8 33
Create snapshot on MSSQL 2012 3 20
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question