T-SQL help

Hi,
I am writing a small TSQL procedure and need a help.

I am not sure how to use a cursor and select records from table matching the variable in an array.

ALTER PROCEDURE test
AS
BEGIN
   CREATE TABLE #test  
(
     datenow  varchar(100)
    ,process_id  varchar(100)
    ,countrecords  varchar(100)
    ,colour  varchar(100)
)

declare @List_Process_Id table
(
process_id varchar(100)
)

insert into @List_Process_Id(process_id) values ('Process1')
insert into @List_Process_Id(process_id) values ('Process2')
insert into @List_Process_Id(process_id) values ('Process3')
insert into @List_Process_Id(process_id) values ('Process4')

select * from @List_Process_Id

declare @i int
declare @cnt int
declare @l_process_id varchar(100)

select @i = min(idx) - 1, @cnt = max(idx) from @List_Process_Id

while @i < @cnt
begin
fetch next from @List_Process_Id into @l_process_id

insert into #test(datenow,process_id,countrecords,colour) 
select convert(varchar,GETDATE(),103) date, @l_process_id,COUNT(*), case when COUNT(*) > 1000 then  'Green' else 'Red' end 
from table1
where process_id=@l_process_id

print @l_process_id

select * from #test

END 

Open in new window

crazywolf2010Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BeartlaoiCommented:
Here is your code using proper cursor:
ALTER PROCEDURE test
AS
BEGIN

   CREATE TABLE #test  
(
     datenow  varchar(100)
    ,process_id  varchar(100)
    ,countrecords  varchar(100)
    ,colour  varchar(100)
)

declare @List_Process_Id table
(
process_id varchar(100)
)

insert into @List_Process_Id(process_id) values ('Process1')
insert into @List_Process_Id(process_id) values ('Process2')
insert into @List_Process_Id(process_id) values ('Process3')
insert into @List_Process_Id(process_id) values ('Process4')

select * from @List_Process_Id

--declare @i int
--declare @cnt int
declare @l_process_id varchar(100)

--select @i = min(idx) - 1, @cnt = max(idx) from @List_Process_Id

DECLARE mycursor CURSOR FOR SELECT process_id FROM @List_Process_Id
OPEN mycursor
FETCH NEXT FROM mycursor INTO @l_process_id
--while @i < @cnt
WHILE @@FETCH_STATUS = 0
BEGIN
	--fetch next from @List_Process_Id into @l_process_id

	insert into #test(datenow,process_id,countrecords,colour) 
	select convert(varchar,GETDATE(),103) date, @l_process_id,COUNT(*), case when COUNT(*) > 1000 then  'Green' else 'Red' end 
	from table1
	where process_id=@l_process_id

	print @l_process_id
	FETCH NEXT FROM mycursor INTO @l_process_id
END 
CLOSE mycursor
DEALLOCATE mycursor
select * from #test
DROP TABLE #test

Open in new window

Note this MS TechNet article which has more examples of how to use cursor
http://technet.microsoft.com/en-us/library/ms180169.aspx
0
PadawanDBAOperational DBACommented:
Sorry, I saw a whole lot more that needed to be addressed so I am re-posting.  The proper use of a cursor goes something like this:

declare tblCursor cursor fast_forward for
     select process_id from @list_process_id;
open tblCursor;

fetch next from tblCursor into @l_process_id;
while @@fetch_status = 0
begin
     < ... whatever you want to do with the value here ... >
     fetch next from tblCursor into @l_process_id;
end

close tblCursor;
deallocate tblCursor;

Open in new window


edit: essentially the same as what the above posted - sry for the double post!
0
Scott PletcherSenior DBACommented:
You don't need a cursor for this.  And cursors have such high overhead that they should be used only when absolutely necessary.  So, in this case, instead do something like this:

insert into #test(datenow,process_id,countrecords,colour)
select
    convert(varchar,GETDATE(),103) date,
    lpi.process_id,
    COUNT(*),
    case when COUNT(*) > 1000 then  'Green' else 'Red' end
from table1 t1
inner join @list_process_id lpi on
    lpi.process_id = t1.process_id

The INNER JOIN will insure that only rows with a matching process id are pulled from table1.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.