Problem setting a Variable.

[Bill Crawley]

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.

--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)

[Shuja Ali]

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 Select @var = COLUMNNAME From Table Inner Join........