February 20, 2009 at 8:25 am
Hi All,
I am building quite a complex cursor and have got stuck near the first hurdle. the compiler doesn't like the way I'm assigning @manufacture. Can someone please tell me what I'm doing wrong. 'Set' doesn't work either:
CREATE PROCEDURE PPMPPFReport_FirstPrintRunVariants
-- Add the parameters for the stored procedure here
@prod_id numeric(15,0),
@version char(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Table for final output
Declare @first_print Table (printrun_percent int, print_run numeric(15),
prod_cost numeric(12,2), freight numeric(12,2),
umc_prod_cost numeric(12,2), umc_freight numeric(12,2),
gross_profit_tot numeric(15), gross_profit numeric(3,2) )
--Cursor variables
Declare @percent_val int
Declare @manufacture numeric(12,2)
Declare @freight numeric(12,2)
Declare @paper numeric(12,2)
Declare percentage_cursor CURSOR for select int_value from tpm_par where par_nm like 'PPF_PRINTRUN_VAR_%'
open percentage_cursor
FETCH NEXT FROM percentage_cursor into @percent_val
WHILE @@FETCH_STATUS = 0
BEGIN
--Manufacture
select @manufacture = select (sum(run_1000)/1000*1250)+SUM(make_rdy) from tpm_ver_imp_manu_cost m
inner join tpm_manu_process p on m.process_id = p.process_id
where prod_id=@prod_id and version=@version and type='S' and impression=0
FETCH NEXT FROM percentage_cursor into @percent_val
END
CLOSE percentage_cursor
DEALLOCATE percentage_cursor
END
GO
February 20, 2009 at 8:49 am
The following should fix your syntax error:
select @manufacture = SUM(run_1000) * 1.25 + SUM(make_rdy)
from tpm_ver_imp_manu_cost m
inner join tpm_manu_process p
on (m.process_id = p.process_id)
where (prod_id = @prod_id)
and (version = @version)
and (type = 'S')
and (impression = 0)
Do you really need to use a cursor here? Unless there's a lot more complexity in the cursor processing loop that you've omitted from the post, a set-based solution should be achievable.
Also, the above expression doesn't use the cursor variable @percent_val so unless something else is going on that has been omitted from the posted TSQL, I would expect this query to return the same value for every loop of the cursor, in which case it doesn't need to be inside the cursor loop.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply