sortby within reporting procedure

Question

sortby within reporting procedure

hren

Ten Centuries

Points: 1220
More actions
July 18, 2005 at 7:19 am

#90315

I have procedure that builds table as next:
CREATE PROCEDURE dbo.ReturnResponseByFormSXML
(
@EventID varchar(500),
@FieldID varchar(500), @SortBy varchar(500)
)
AS
DECLARE @SqlString nvarchar(4000)
DECLARE @SqlString2 nvarchar(4000)
set @Sqlstring2 = '
SELECT @Sql = coalesce(@Sql,'''') + '' ISNULL(min(case A.Name when '''''' + DT.Name + '''''' then Response end),0) as ['' + DT.Name + ''],''
FROM (select distinct REPLACE(a.name, '' '', ''_'') as Name
      from #Temp1 A
      JOIN #Temp1Response B on A.fieldId = b.FieldID
      WHERE a.FieldID in (' + @FieldID + ')
        AND B.EventID in (' + @EventID + ')) DT '
exec sp_executeSql @SqlString2, N'@Sql varchar(8000) OUTPUT ', @SqlString OUTPUT
Set @SqlString = SUBSTRING(@SqlString,1, len(@SqlString) -1)
SET @SqlString = 'SELECT ISNULL(E.EventName, 0) AS EventName, ISNULL(LastName, 0) AS LastName, ISNULL(FirstName, 0) AS FirstName, ISNULL(Organization, 0) AS Organization, D.Title, ' + @SqlString + '
   FROM #Temp1 A
   JOIN #Temp1Response B on A.fieldId = b.FieldID
   LEFT JOIN UserInfo C on B.UserID = C.UserID
   JOIN Forms D on A.FormID = D.FormID
   LEFT JOIN Events E on B.EventID = E.EventID
   WHERE A.FieldID in (' + @FieldID + ') AND B.EventID in (0,' + @EventID + ')
   Group by EventName, LastName, FirstName, Organization, D.Title
   Order by EventName'
exec sp_executeSql @SqlString, N'@FieldID varchar(500), @EventID varchar(500)', @FieldID, @EventID
As you can see it has @SortBy that is not in use. It being passed into procedure from VB as 7250,1;7251,2;7254,1 for example. Where 7251, 7251, 7254 is pretty much a column in final table by which it can be sorted(User can choose up to 3 columns to sort by) and ,1 or ,2 to choose ASC or DESC order to sort it by.
So how can I add this feature to this procedure so that it would pick up @SortBy and sort final table by it?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

Instead of the @SortBy parameter, I think I would add 6 parameters, @VarName1, @VarName2, @VarName3, @SortOrder1, @SortOrder2, @SortOrder3 and then add something like

'order by ' + @VarName1 + ' ' + @SortOrder1 + ', ' + @VarName2 + ' ' + @SortOrder2 + ', ' + @VarName3 + ' ' + @SortOrder3

to your @SqlString. This way you avoid string manipulations in T-SQL.

hren Ten Centuries Points: 1220 More actions · Answer 2

Would that be ok though if user decides to sort only by one column? Cuz that way only 'xxxx,x' would be passed in.

if that would be ok, could you show me how to do that?

Cuz it's passed in in format as 'xxxx,x; xxx,x; xxxx,x; xxxx,x' - I guess I need to parse that string in somehow and brake it(delimiter) - but I have no clue how to do that.

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 3

You could input @VarName1=@VarName2=@VarName3, or more elegantly, @VarName2=@VarName3=null and then

declare @OrderBy varchar(1000)

select @OrderBy = ''

if @VarName1 is not null

select @OrderBy = @VarName1 + ' ' + @SortOrder1

if @VarName2 is not null

select @OrderBy = @OrderBy + ', ' + @VarName2 + ' ' + @SortOrder2

if @VarName3 is not null

select @OrderBy = @OrderBy + ', ' + @VarName3 + ' ' + @SortOrder3

If you insist on parsing the string in T-SQL you may start out with something like

select substring(@s, 1, charindex(';', @s-2) - 1)

select substring(@s, charindex(';', @s-2) + 1, len(@s))

But my point is that the application layer (i.e your VB code) is a better place to do such string manipulations.