Order by, Using a param

How do you allow sorting via one of the params?
I would like to do something like...
declare @.c char(50)
,@.s char(30)
set @.c = 'SomeClient'
set @.s = 'SomeColumn'
select @.s
select *
from MyTable
where rtrim(clientName) = rtrim(@.c)
order by rtrim(@.s)
thanks in advance..
bob.John 3:16 wrote:
> How do you allow sorting via one of the params?
> I would like to do something like...
> declare @.c char(50)
> ,@.s char(30)
> set @.c = 'SomeClient'
> set @.s = 'SomeColumn'
> select @.s
> select *
> from MyTable
> where rtrim(clientName) = rtrim(@.c)
> order by rtrim(@.s)
>
> thanks in advance..
> bob.
>
DECLARE @.ClientName VARCHAR(50)
DECLARE @.ColumnName VARCHAR(30)
DECLARE @.Command VARCHAR(1024)
SELECT @.ClientName = 'SomeClient'
SELECT @.ColumnName = 'SomeColumn'
SELECT @.Command = 'SELECT * FROM MyTable WHERE clientName = @.ClientName
ORDER BY ' + @.ColumnName
EXEC (@.Command)|||http://www.aspfaq.com/2501
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:egtoAL7kGHA.4816@.TK2MSFTNGP05.phx.gbl...
> How do you allow sorting via one of the params?
> I would like to do something like...
> declare @.c char(50)
> ,@.s char(30)
> set @.c = 'SomeClient'
> set @.s = 'SomeColumn'
> select @.s
> select *
> from MyTable
> where rtrim(clientName) = rtrim(@.c)
> order by rtrim(@.s)
>
> thanks in advance..
> bob.
>|||Thanks Tracy...
I really appreciate it.
Bob.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23TIRzN7kGHA.5108@.TK2MSFTNGP02.phx.gbl...
> John 3:16 wrote:
> DECLARE @.ClientName VARCHAR(50)
> DECLARE @.ColumnName VARCHAR(30)
> DECLARE @.Command VARCHAR(1024)
> SELECT @.ClientName = 'SomeClient'
> SELECT @.ColumnName = 'SomeColumn'
> SELECT @.Command = 'SELECT * FROM MyTable WHERE clientName = @.ClientName
> ORDER BY ' + @.ColumnName
> EXEC (@.Command)|||Thanks Aaron...
I checked out the link....
I appreciate the reply and the link.
Bob.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:upqE0N7kGHA.2052@.TK2MSFTNGP04.phx.gbl...
> http://www.aspfaq.com/2501
>
> "John 3:16" <bobmcc@.tricoequipment.com> wrote in message
> news:egtoAL7kGHA.4816@.TK2MSFTNGP05.phx.gbl...
>|||"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:es2VKR7kGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Thanks Tracy...
> I really appreciate it.
> Bob.
If your ORDER BY column name is not hard-wired into your script (highly
likely or you wouldn't need to use Dynamic SQL to generate the SELECT
statement), be very careful that you validate the column name thoroughly or
you can leave yourself open to SQL Injection, as in:
SELECT @.ColumnName = '1; TRUNCATE TABLE SomeImportantTable; --'

Order By Param

Is there a way to have a report parameter for the selection of what field
the user wants a table to be grouped on? For instance, the parameter would
have 3 options, clientid, clientname, and clientcity. I want to be able to
select one of these and have the table group on this selection.
Or any generic ways or ideas on how to accomplish this?
Thanks!!!I am looking for the same solution. I have a client that I have promised 10
reports to and they gave me a few but want sort capability on 5 different
fields. I could say that those are 5 different reports, but there should be
an easy way to list the fields in a drop down parameter and adjust the ORDER
BY based on this. Shouldn't there?
Thanks in advance.
ANDY
"Amon Borland" wrote:
> Is there a way to have a report parameter for the selection of what field
> the user wants a table to be grouped on? For instance, the parameter would
> have 3 options, clientid, clientname, and clientcity. I want to be able to
> select one of these and have the table group on this selection.
> Or any generic ways or ideas on how to accomplish this?
> Thanks!!!
>
>|||can you pass the field to sort on into the report as a parameter and use a
CASE in your query to conditionally sort based on whatever you passed in as
a parameter?
Bill
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:C80B4FC3-FA92-4ADE-BFEA-D6B0867FDE97@.microsoft.com...
> I am looking for the same solution. I have a client that I have promised
10
> reports to and they gave me a few but want sort capability on 5 different
> fields. I could say that those are 5 different reports, but there should
be
> an easy way to list the fields in a drop down parameter and adjust the
ORDER
> BY based on this. Shouldn't there?
> Thanks in advance.
> ANDY
> "Amon Borland" wrote:
> > Is there a way to have a report parameter for the selection of what
field
> > the user wants a table to be grouped on? For instance, the parameter
would
> > have 3 options, clientid, clientname, and clientcity. I want to be able
to
> > select one of these and have the table group on this selection.
> >
> > Or any generic ways or ideas on how to accomplish this?
> >
> > Thanks!!!
> >
> >
> >|||It seems all my replied are this..
Sorting is easy
DECLARE @.sql as varchar(100)
SET @.sql = 'SELECT * FROM blah WHERE blah = blah ORDER BY '
@.paramete
EXEC sp_executesql @.sq
Grouping on the other hand I don't think you can do dynamically