May 22, 2011 at 7:48 am
Hi,
For the past two days, I was trying to manipulate the results from test table to test_temp table using the fetch and cursor but can't get the results and hit a lot of errors. Please find the tables and my sql below. The rule here is; if there is a NULL value in subgroup column for SMEAST unit and BASE data_view, it will add additional two more rows and append SWHGRP and SMASGRP in subgroup.
test table
organization data_view subgroup amount
EAGLE INTERCO SMASGRP 10
EAGLE OWNER SWHGRP 15
SMEAST BASE NULL 5
SMEAST OWNER SWHGRP 18
test_temp table
organization data_view subgroup amount
EAGLE INTERCO SMASGRP 10
EAGLE OWNER SWHGRP 15
SMEAST BASE NULL 5
SMEAST BASE SWHGRP 10
SMEAST BASE SMASGRP 5
SMEAST OWNER SWHGRP 18
The sql code:
declare
@organization varchar(16),
@data_view varchar(16),
@subgroup varchar(16),
@i int
set @i=0
DECLARE org_cursor CURSOR FOR
select organization, data_view, subgroup from test
open org_cursor
fetch next from org_cursor
into @organization, @data_view, @subgroup
while @@fetch_status = 0
begin
set @i=@i+1
if @i=1
begin
exec('
drop table test_temp
if @organization = ''SMEAST'' and @data_view = ''BASE'' and @subgroup is NULL
begin
SELECT organization, data_view, ''SWHGRP'' as subgroup, amount*2 as amount
into test_temp
FROM test
insert into test_temp
SELECT organization, data_view, ''SMASGRP'' as subgroup, amount
FROM test
end
else
begin
SELECT organization, data_view, subgroup, amount
into test_temp
FROM test
end
')
end
else
begin
if @i>1
exec('
if @organization = ''SMEAST'' and @data_view = ''BASE'' and @subgroup is NULL
begin
insert into test_temp
SELECT organization, data_view, ''SWHGRP'' as subgroup, amount*2 as amount
FROM test
insert into test_temp
SELECT organization, data_view, ''SMASGRP'' as subgroup, amount
FROM test
end
else
begin
insert into test_temp
SELECT organization, data_view, subgroup, amount
FROM test
end
')
end
fetch next from org_cursor
into @organization, @data_view, @subgroup
end
close org_cursor
deallocate org_cursor
select * from test_temp
the error:
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@organization".
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'else'.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@organization".
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'else'.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@organization".
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'else'.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@organization".
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'else'.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@organization".
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'else'.
Hope someone here can enlighten me. Thanks!
May 22, 2011 at 9:51 am
Long story short, dynamic sql runs on a different connection. So everything you created in the proc needs to either be put in the dynamic sql via concatenation OR needs to be passed via sp_executesql.
What exactly are you trying to do that requires dynamic sql... I don't see any need for it at the moment!
May 22, 2011 at 10:25 am
Or, get rid of the c.u.r.s.o.r. and the dynamic SQL in the first place and use a set based solution:
DECLARE @tbl TABLE
(
organization VARCHAR(10),
data_viewVARCHAR(10),
subgroup VARCHAR(10),
amount INT
)
INSERT INTO @tbl
VALUES ('EAGLE','INTERCO','SMASGRP', 10),
('EAGLE','OWNER','SWHGRP',15),
('SMEAST','BASE', NULL, 5),
('SMEAST','OWNER','SWHGRP',18)
SELECT *
FROM @tbl
;WITH cte AS
( SELECT organization, data_view,amount
FROM @tbl
WHERE subgroup IS NULL
)
INSERT INTO @tbl
SELECT organization, data_view,ca.subgroup, ca.multiply * amount
FROM cte
CROSS APPLY
(SELECT 'SWHGRP' AS subgroup, 2 AS multiply UNION ALL
SELECT 'SMASGRP',1) ca
SELECT *
FROM @tbl
May 24, 2011 at 7:10 am
Hi,
Modified the given query. Now its running perfectly.
declare
@organization varchar(16),
@data_view varchar(16),
@subgroup varchar(16),
@i int
set @i=0
DECLARE org_cursor CURSOR FOR
select organization, data_view, subgroup from test
open org_cursor
fetch next from org_cursor
into @organization, @data_view, @subgroup
while @@FETCH_STATUS = 0
begin
set @i=@i+1
if @i=1
begin
exec ('drop table test_temp')
if @organization = 'SMEAST' and @data_view = 'BASE' and @subgroup is NULL
begin
SELECT organization, data_view, 'SWHGRP' as subgroup, amount*2 as amount
into test_temp
FROM test
insert into test_temp
SELECT organization, data_view, 'SMASGRP' as subgroup, amount
FROM test
end
else
begin
SELECT organization, data_view, subgroup, amount
into test_temp
FROM test
end
end
else
begin
if @i>1
if @organization = 'SMEAST' and @data_view = 'BASE' and @subgroup is NULL
begin
insert into test_temp
SELECT organization, data_view, 'SWHGRP' as subgroup, amount*2 as amount
FROM test
insert into test_temp
SELECT organization, data_view, 'SMASGRP' as subgroup, amount
FROM test
end
else
begin
insert into test_temp
SELECT organization, data_view, subgroup, amount
FROM test
end
end
fetch next from org_cursor
into @organization, @data_view, @subgroup
end
close org_cursor
deallocate org_cursor
select * from test_temp
Regards
Siva Kumar J
May 24, 2011 at 7:14 am
What was wrong with Lutz's solution?
May 24, 2011 at 7:45 am
Hi sivaj2k and LutzM,
Thanks for the different solution...had tested and both works like a charm...now i know which part had gone wrong.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply