I have three tables, where one of the tables is joined by an employeeId. A normal sql statement makes the join and returns three values in one column - however, I need to display these values as a single String, ie instead of
Harry Tuttle
Sam Lowrey
Jack Lint
I need to return as
Harry Tuttle, Sam Lowrey, Jack Lint
I've created a stored proc and am using a cursor (my first attempt), which is included below. THe only issue is that it execytes and returns
Sam Lowrey,
on every execute. The proc looks like
create procedure sp_ac_temp (@assignmment_id int)
as
/* declare local variables used for fetch */
declare @advisor_name varchar(100)
/* cursor to get each advisor name */
declare advisor_fetch cursor for
select e.first_name + ' ' + e.last_name + ', '
from assign a
inner join advisor fa on a.assignment_id = fa.assignment_id
inner join employee e on fa.employee_id = e.employee_id
where a.assignment_id = @assignmment_id
open advisor_fetch
fetch advisor_fetch
into @advisor_name
if (@@sqlstatus = 2)
begin
close advisor_fetch
return
end
/* if cursor result set is not empty, process each row of information */
while (@@sqlstatus = 0)
begin
--if (@advisor_name != NULL)
begin
select @advisor_name = @advisor_name + '! '
end
fetch advisor_fetch into @advisor_name
select @advisor_name
end
I can alter the script and generate output on different SquirrleSQL panes eg if I alter the while loop segment to:
while (@@sqlstatus = 0)
begin
--if (@advisor_name != NULL)
begin
select @advisor_name = @advisor_name + '! '
select @advisor_name --this here
end
fetch advisor_fetch into @advisor_name
select @advisor_name
end
this shows the cursor is looping 6 times (I get 6 IDE frames generated, each containing one name but they vary:
Harry Tuttle, !
Sam Lowrey,
Sam Lowrey, !
Jack Lint,
Jack Lint,
Jack Lint, !
)).
I'm using Sybase. Any ideas?
You keep overwriting your @advisor_name
variable. You need to accumulate the answer into a different variable, here @combined_name
.
I don't know sybase, but I also assume you need to close/deallocate the cursor after using it.
create procedure sp_ac_temp (@assignmment_id int) as
declare @advisor_name varchar(100)
declare @combined_name varchar(1000) -- variable to accumulate answer
select @combined_name = ''
declare advisor_fetch cursor for
select
e.first_name + ' ' + e.last_name + ', '
from
assign a inner join
advisor fa on a.assignment_id = fa.assignment_id inner join
employee e on fa.employee_id = e.employee_id
where
a.assignment_id = @assignmment_id
open advisor_fetch
fetch advisor_fetch into @advisor_name
if (@@sqlstatus = 2)
begin
close advisor_fetch
return
end
/* if cursor result set is not empty, process each row of information */
while (@@sqlstatus = 0)
begin
select @combined_name = @combined_name + @advisor_name --append next advisor
fetch advisor_fetch into @advisor_name
end
select @combined_name