Dynamic Sort using proc in MS SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Sort using proc in MS SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-01 : 12:39:58

jennifer writes "hello I need help with this proc.

Select @p_Sort1 = 'Amount'
Select @p_Sort2 = 'Company'
Select @p_Sort3 = 'Customer'
--Select @p_Sort4 = 'Writing_Agent'
Select @p_Sort4 = NULL
Select @p_Sort5 = 'desc'

declare @sortorder int
declare @str varchar(1000)

select @sortorder = 4

IF @p_Sort1 = NULL
SET @sortorder = @sortorder - 1

IF @p_Sort2 = NULL
SET @sortorder = @sortorder - 1

IF @p_Sort3 = NULL
SET @sortorder = @sortorder - 1

IF @p_Sort4 = NULL
SET @sortorder = @sortorder - 1
print CONVERT(char(10), @sortorder)

if @p_Sort5 = 'asc'
Select @str =
CASE @sortorder
WHEN 4
THEN 'SELECT Writing_Order_Date, Company_ID, Company, Policy,
Customer, Writing_Agent, Writing_Agent_Name, SplitAgent,
SplitAgent_Name, Lvl, LevelPercent, AgentPercent, Amount,
Source, Break_Type, Policy_Age, Category,Commission_Base,
Field_Percent,Division
FROM CommissionAgentCompanyDetail_New (readpast)
WHERE Country = ' + '''' + @Country + '''' +
' AND Agent_ID = ' + '''' + @Agent_ID + '''' +
' AND Balance_Date = ' + '''' + CONVERT(char(10), @Balance_date, 103) + '''' +
' order by ' + @p_Sort1 + ', ' + @p_Sort2 + ', ' + @p_Sort3 + ', ' + @p_Sort4
WHEN 3
run code
WHEN 2
code
etc

My problem starts with running the if statment and i set the @p_Sort4 = NULL i only get a number 4 for my results. It keeps crapping out on the IF statement if one of the values is null.

Basically this needs to see what values the agent picks to sort by and has 4 options to sort by. If the same value is selected this proc won't work.

Any suggestions would be great. thanks"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-02 : 02:22:58

sorting or anything that will 'beautify' the data set is done on the apps side, you get more flexibility and can play around with the data set produced

also applying dsql for sorting purpose will not give you any benefits aside from having your data sorted the way you want it to look

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-02 : 02:36:14

1. IF @p_Sort1 IS NULL

2. ' order by ' + coalesce(@p_Sort1 + ', ', '') + coalesce(@p_Sort2 + ', ', '') + coalesce(@p_Sort3 + ', ', '') + coalesce@p_Sort4, '')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-02 : 04:28:42

Also, IF @p_Sort1 = NULL should be IF @p_Sort1 IS NULL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources