July 18, 2005 at 7:19 am
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?
July 18, 2005 at 9:08 am
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.
July 18, 2005 at 9:15 am
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.
July 19, 2005 at 1:41 am
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