help me get rid of the cursor 2

[Newbi1]

I think I can do this with a case statement, however, I am not 100% sure of the syntax or how to do this.

What I have done so far is created a temp table with all the fields I need. I have 20 user defined fields that I need to export as a single record from a 1-many relationship.

I started doing this with a cursor, but there has to be a better way.

Create table #test 
        PROIDNO VARCHAR(20),
        PROUD01 varchar(30),
	PROUD02 varchar(20),
	PROUD03 varchar(10),
	PROUD04 varchar(50)
INSERT INTO TEST (proidno) select PROIDNO FROM TABLE PROJECT


Declare @proidno varchar(30),
	@empid varchar(20),
	@role varchar(10),
	@desc varchar(50)
 
declare pro1 cursor for select distinct proidno, employee, role, [description] from
dbo.cfgemployeerole, dbo.emprojectassoc
where dbocfgemployeerole.code = dbo.emprojectassoc.role
order by proidno

open pro1
fetch next from pro1 into
	@proidno,
	@empid,
	@role,
	@desc

while @@fetch_status = 0

begin

If @role = 'a' then 
update #test set PROUD01 = @EMPID where proidno = @proidno
else
if @role = b then 
update #test set PROUD02 = @EMPID where proidno = @proidno
END

fetch next from pro1 into
	@proidno,
	@empid,
	@role,
	@desc

end

Would there be a way to do this from the intial insert? or through a single pudate?

 

[vongrunt]

Is it possible that cursor encounters more than one row with the same proidno?

If true, how do you want to update proud01/02?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Newbi1]

yes, there will be several rows with the same proidno. I need some kind of way to take every row (100000) and send those to the output table.

I will not have any rows with the same Proidno and role and different employees.

I was hoping to do one loop through this or have it processed during the insert or an update statement. (Maybe with some kind of case statement???)

The cursor runs for over 2 hours and that is just niot acceptable. There has to be a better way.

 

[vongrunt]

> I will not have any rows with the same Proidno and role and different employees.

Kewl. Then try something like:

update t
set PROUD01 = d.employee
from #test t
inner join 
(	select distinct proidno, employee, [description] from
	dbo.cfgemployeerole, dbo.emprojectassoc
	where dbocfgemployeerole.code = dbo.emprojectassoc.role
		and role = 'a'
) d on t.proidno = d.proidno

... and repeat this query for PROUD02 and role = 'b'.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[hmckillop]

does this do the same?

Create table #test 
  (      PROIDNO VARCHAR(20),
        PROUD01 varchar(30),
    PROUD02 varchar(20),
    PROUD03 varchar(10),
    PROUD04 varchar(50))
INSERT INTO #test (proidno) select PROIDNO FROM TABLE PROJECT

UPDATE tmp 
SET PROUD01 = CASE WHEN tmp.role = 'a' then @EMPID else PROUD01 END,
	PROUD02 = CASE WHEN tmp.role = 'b' then @EMPID else PROUD02 END
FROM #test tst
INNER JOIN 
	(select distinct proidno, employee, role, [description] from
	dbo.cfgemployeerole, dbo.emprojectassoc
	where dbocfgemployeerole.code = dbo.emprojectassoc.role) tmp on tmp.proidno = tst.proidno

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

pipped to the post again.

Mental Note - Start quicktyping lessons.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[vongrunt]

> Mental Note - Start quicktyping lessons.

My solution for that problem is good ol' IBM keyboard found at local IT junkyard.

IMO both methods will do the job - your with one pass, mine with two passes and no dummy updates.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Newbi1]

Thanks guys... Both a real help. I have to start finding a specialty. 3 weeks in access, 1 in sql, then back to crystal and VB then back to SQL.

My mind starts to get sooo confused at times, but y'all are lifesavers.