SELECT		ed.tpa_id,
		ed.division_key,
		emd.tpa_id,
		emd.empe_key,
		ISNULL(rctpa_id, ed.tpa_id)
from		dbo.EMPLOYER_DIVISION AS ED,
LEFT join	EMPLOYEE_MISC_DATA AS EMD ON emd.tpa_id = ed.tpa_id and emd.empe_key = ed.division_key

SELECT		ed.tpa_id,
		ed.division_key,
		emd.tpa_id,
		emd.empe_key,
		case
			when @rc = 1 then RCONE
			when @rc > 2 then RCTWO
			ELSE NULL
		END
from		dbo.EMPLOYER_DIVISION AS ED
inner join	EMPLOYEE_MISC_DATA AS EMD on emd.tpa_id=ed.tpa_id and emd.empe_key=ed.division_key
order by	5

CREATE procedure GetDivisionKeyAssigEmployee
(
	@empid varchar(20),
	@tpid varchar(20),
	@divstr varchar(20),
	@rs tinyint OUTPUT
)
as

SET NOCOUNT ON

declare	@ekey char(2),
	@divs2 varchar(10)

select @ekey = empe_key
from	vw_employee
where	tpa_id = @tpid
	and empr_id = @empid

select	@divs2 = division_key
from	vw_employee_misc_data
where	tpa_id = @tpid
	and empe_key = @ekey

if @divs2 = substring(@divstr, 1, charindex(',', @divstr, 1) - 1)
	select @rs = 1
else
	if @divs2 = substring(@divstr, charindex(',', @divstr, 1) + 1, 2)
		select @rs = 1
	else
		if @divs2 = substring(@divstr, charindex(',', @divstr, charindex(',', @divstr, 1) + 1), 2)
			select @rs = 1
		else
			select @rs = 0

quote:

---

Originally posted by Peso

Don't change the original posting. Now we have no idea what you are trying to achieve.
What is CASE 1 and what is CASE 2?
What is output record?



Peter Larsson
Helsingborg, Sweden

---