Problem setting a Variable.

Guys,

In a Transaction Block I have the following code, and in my catch block I report the error. It States that on line 27 (reference to the first variable being set) That the Subquery returned more than 1 value which is not permitted.

When I run the queries individually sustituting the @TheCaller variable Both queries return 1 value as I'd expect and although it states line 27, I think it's a red herring since the subqueries are on the more complex query. I then wrapped the whole lot in a 'Select top 1' statement, but I still receive the same error. Again if I run the individual subqueries on their own again I only receive 1 record. This is really baffling me as I really cannot see what is wrong.

Code:

---

                --PROF_SUBTYPE_ID = 3 -- customer
                set @ProfDetId = (Select PROF_DET_ID from dbo.TB_ProfileDetail
                Inner Join dbo.TB_ProfileHeader on dbo.TB_ProfileHeader.PROF_ID = dbo.TB_ProfileDetail.PROF_ID
                Inner Join dbo.TB_ProfileType on dbo.TB_ProfileType.PROF_TYPE_ID = dbo.TB_ProfileHeader.PROF_TYPE_ID
                where TB_ProfileDetail.PROF_ID = @TheCaller
                AND dbo.TB_ProfileType.PROF_SUBTYPE_ID = 3)

                --PROF_SUBTYPE_ID = 1 -- invoice
                set @InvDetId = (Select PROF_DET_ID from dbo.TB_ProfileDetail
                Inner Join dbo.TB_ProfileHeader on dbo.TB_ProfileHeader.PROF_ID = dbo.TB_ProfileDetail.PROF_ID
                Inner Join dbo.TB_ProfileType on dbo.TB_ProfileType.PROF_TYPE_ID = dbo.TB_ProfileHeader.PROF_TYPE_ID
                where TB_ProfileDetail.ind_ID
                        in (Select ind_ID from dbo.TB_ProfileDetail
                        Inner Join dbo.TB_ProfileHeader on dbo.TB_ProfileHeader.PROF_ID = dbo.TB_ProfileDetail.PROF_ID
                        Inner Join dbo.TB_ProfileType on dbo.TB_ProfileType.PROF_TYPE_ID = dbo.TB_ProfileHeader.PROF_TYPE_ID
                        AND TB_ProfileDetail.PROF_ID = @TheCaller)
                AND dbo.TB_ProfileType.PROF_SUBTYPE_ID = 1
                union
                Select PROF_DET_ID from dbo.TB_ProfileDetail
                Inner Join dbo.TB_ProfileHeader on dbo.TB_ProfileHeader.PROF_ID = dbo.TB_ProfileDetail.PROF_ID
                Inner Join dbo.TB_ProfileType on dbo.TB_ProfileType.PROF_TYPE_ID = dbo.TB_ProfileHeader.PROF_TYPE_ID
                where TB_ProfileDetail.COMP_ID
                        in (Select COMP_ID from dbo.TB_ProfileDetail
                        Inner Join dbo.TB_ProfileHeader on dbo.TB_ProfileHeader.PROF_ID = dbo.TB_ProfileDetail.PROF_ID
                        Inner Join dbo.TB_ProfileType on dbo.TB_ProfileType.PROF_TYPE_ID = dbo.TB_ProfileHeader.PROF_TYPE_ID
                        AND TB_ProfileDetail.PROF_ID = @TheCaller)
                AND dbo.TB_ProfileType.PROF_SUBTYPE_ID = 1)

---

Well SQL is right, your sub-query can return multiple values too, this is why SQL is complaining about it. Before execute the SQL will compile the query and this compilation causes the query(or block of SQL code) throw an error.

The proper way of doing that would be

Code:

---

Select @var = COLUMNNAME From Table Inner Join........

---