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