sortby within reporting procedure

  • 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?

  • 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.

  • 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.

  • 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.

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

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