Jeff Shain
Reputation: 775
Adding order by to a cursor SQL server 2005
I have this query:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_ANSWER_GRID]
@league int,
@league_survey_id int,
@page_size int,
@page_num int,
@orderby varchar(50),
@f_division_id int,
@myf_group_id int,
@searchname varchar(250)
AS
--These will be sproc params:
--declare @page_size int;
--declare @page_num int;
--declare @league_survey_id int;
--declare @orderby varchar(50);
--set @league_survey_id = 340;
--set @page_size=419;
--set @page_num=0;
--set @orderby = 'f_member_id';
----
declare @query varchar(max);
declare @joins varchar(max);
declare @groupaddon varchar(max);
declare @membersearchaddon varchar(max);
declare @questionid varchar(50);
declare @question varchar(50);
declare @question_type int;
declare @minrow int;
declare @maxrow int;
declare @i int;
if @myf_group_id= 0
begin
set @groupaddon = ''
end
else
begin
set @groupaddon = 'and f_group_id = ' + convert(varchar(50),@myf_group_id) + ''
end
set @minrow = @page_size * @page_num;
if len(@searchname) = 0
begin
set @membersearchaddon = ''
end
else
begin
set @membersearchaddon = 'and str_last_name like ''' + convert(varchar(250),@searchname) + '%'''
end
set @maxrow = ((@page_num + 1) * @page_size);
-- one sec
set @i = 0;
set @query = 'select * from (select *, ROW_NUMBER() OVER (ORDER BY ' + @orderby + ') as RANK from (select
a.f_member_id,a.f_member_id as bulk_f_member_id,a.f_member_id as p_member_id,
( select max(ans.dte_added)
from tb_league_survey_answers ans with(nolock)
where ans.f_member_id = a.f_member_id
and ans.f_league_survey_id = ' + convert(varchar(50),@league_survey_id) + ') dte_added,
( select top 1 ans.f_division_id
from tb_league_survey_answers ans with(nolock)
where ans.f_member_id = a.f_member_id
and ans.f_division_id != 0
and ans.f_league_survey_id = ' + convert(varchar(50),@league_survey_id) + ') f_division_id,
( select top 1 dues_collected.int_division_fees_collected
from tb_league_dues_collected dues_collected with(nolock)
inner join tb_league_dues l_dues with(nolock) on (dues_collected.f_dues_id = l_dues.p_dues_id)
where dues_collected.f_league_id = ' + convert(varchar(50),@league) + '
and l_dues.f_league_id = ' + convert(varchar(50),@league) + '
and l_dues.bln_archived = 0
and dues_collected.f_division_id = (select top 1 ans.f_division_id from tb_league_survey_answers ans with(nolock) where ans.f_member_id = a.f_member_id and ans.f_league_survey_id = ' + convert(varchar(50),@league_survey_id) + ' and ans.f_division_id !=0)
and dues_collected.f_member_id = a.f_member_id
order by l_dues.p_dues_id desc
) int_division_fees_collected,
( select top 1 dues_collected.int_amount_collected
from tb_league_dues_collected dues_collected with(nolock)
inner join tb_league_dues l_dues with(nolock) on (dues_collected.f_dues_id = l_dues.p_dues_id)
where dues_collected.f_league_id = ' + convert(varchar(50),@league) + '
and l_dues.bln_archived = 0
and l_dues.f_league_id = ' + convert(varchar(50),@league) + '
and dues_collected.f_division_id = (select top 1 ans.f_division_id from tb_league_survey_answers ans with(nolock) where ans.f_member_id = a.f_member_id and ans.f_league_survey_id = ' + convert(varchar(50),@league_survey_id) + ' and ans.f_division_id !=0)
and dues_collected.f_member_id = a.f_member_id
order by l_dues.p_dues_id desc
) int_amount_collected,
( select top 1 dues_collected.bln_master_transaction
from tb_league_dues_collected dues_collected with(nolock)
inner join tb_league_dues l_dues with(nolock) on (dues_collected.f_dues_id = l_dues.p_dues_id)
where dues_collected.f_league_id = ' + convert(varchar(50),@league) + '
and l_dues.bln_archived = 0
and l_dues.f_league_id = ' + convert(varchar(50),@league) + '
and dues_collected.f_division_id = (select top 1 ans.f_division_id from tb_league_survey_answers ans with(nolock) where ans.f_member_id = a.f_member_id and ans.f_league_survey_id = ' + convert(varchar(50),@league_survey_id) + ' and ans.f_division_id !=0)
and dues_collected.f_member_id = a.f_member_id
order by l_dues.p_dues_id desc
) bln_master_transaction,
( select max(cast(bln_multiple_payments_order as int)) as bln_multiple_payments_order
from tb_league_dues_collected with(nolock)
where XID in (
select distinct(XID) as XID
from tb_league_dues_collected dues_collected with(nolock)
inner join tb_league_dues l_dues with(nolock) on (dues_collected.f_dues_id = l_dues.p_dues_id)
where dues_collected.f_league_id = ' + convert(varchar(50),@league) + '
and l_dues.bln_archived = 0
and l_dues.f_league_id = ' + convert(varchar(50),@league) + '
and dues_collected.f_division_id = (select top 1 ans.f_division_id from tb_league_survey_answers ans with(nolock) where ans.f_member_id = a.f_member_id and ans.f_league_survey_id = ' + convert(varchar(50),@league_survey_id) + ' and ans.f_division_id !=0)
and dues_collected.f_member_id = a.f_member_id
)
) bln_multiple_payments_order,
a.f_group_id,a.str_group_name,a.str_division,a.str_officer_title,a.p_group_membership_id,a.bln_participation_status,a.str_badge_id,a.master_first_name,a.master_last_name,a.master_email,a.str_phone_number,a.team_creator_id,a.team_admin_list,a.dte_user_birthday,a.str_email_current,a.str_first_name,a.str_middle_name,a.str_last_name,a.f_submember_of'
set @joins = ''
declare cur cursor local for
(
select distinct convert(varchar(50), p_league_survey_question_id), str_question, str_question_type
from tb_league_survey_questions with(nolock)
where f_league_survey_id = @league_survey_id
);
open cur
fetch next from cur into @questionid, @question, @question_type
while (@@fetch_status = 0)
begin
set @joins = @joins + '
left outer join tb_league_survey_answers q' + convert(varchar(50), @i) + ' with(nolock) on q' + convert(varchar(50), @i) + '.f_league_survey_question_id = ' + @questionid + ' and q' + convert(varchar(50), @i) + '.f_member_id = a.f_member_id'
if (@question_type <> 4)
begin
set @query = @query + ', q' + convert(varchar(50), @i) + '.str_answer as [question' + @questionid + '] '
end else begin
set @query = @query + ', qDiv' + convert(varchar(50), @i) + '.str_division as [question' + @questionid + '] '
set @joins = @joins + '
left outer join tb_league_team_divisions qDiv' + convert(varchar(50), @i) + ' with(nolock) on qDiv' + convert(varchar(50), @i) + '.p_division_id = convert(int, q' + convert(varchar(50), @i) + '.str_answer)'
end
--from tb_league_team_divisions where p_division_id = ' + convert(varchar(50), @divisionid) + ')
fetch next from cur into @questionid, @question, @question_type
set @i = @i + 1;
end
close cur
deallocate cur
set @query = @query + ' from vw_league_members a with(nolock) ' + @joins + ' WHERE exists
(
select *
from tb_league_survey_answers ans with(nolock)
where ans.f_member_id = a.f_member_id
and a.f_league_id = <league_placeholder>
and ans.f_league_survey_id = ' + convert(varchar(50),@league_survey_id) + '
and ans.f_division_id = ' + convert(varchar(50),@f_division_id) + '
<group_placeholder>
<membersearch_placeholder>
)
) data ) dataouter where RANK > ' + convert(varchar(50), @minrow) + ' AND RANK <= ' + convert(varchar(50), @maxrow)
set @query = replace(@query, '<group_placeholder>', @groupaddon)
set @query = replace(@query, '<membersearch_placeholder>', @membersearchaddon)
set @query = replace(@query, '<league_placeholder>', @league)
--print @query;
execute(@query);
What I need to do is add an order by to this part of the t-sql
declare cur cursor local for
(
select distinct convert(varchar(50), p_league_survey_question_id), str_question, str_question_type
from tb_league_survey_questions with(nolock)
where f_league_survey_id = @league_survey_id
);
but I get
Msg 156, Level 15, State 1, Procedure SP_ANSWER_GRID, Line 119
Incorrect syntax near the keyword 'order'.
What am I doing wrong?
Upvotes: 0
Views: 944
Answers (1)
Mikael Eriksson
Reputation: 138990
Remove the parentheses
declare cur cursor local for
select distinct convert(varchar(50), p_league_survey_question_id), str_question, str_question_type
from tb_league_survey_questions with(nolock)
where f_league_survey_id = @league_survey_id
order by 1
Upvotes: 1