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)