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; --'

oracle linked server decimal problem

I'm attempting to query a linked server (Oracle) via QA. My query returns the expected record set, but the data for the decimal columns is not correct (only displays zeros to the right of the decimal point). My T-SQL:
SELECT amount
FROM oracle_src...budget
... yields the result:
..000
... whereas the equivalent query via MS Access yields
2784.56
The data type for the amount column in Oracle is defined as Numeric(9,3).
Any thoughts? I could pass the whole query process through MS Access to Oracle, but ... seems like there should be a more elegant solution. Thanks for any suggestions!
- Denis
I may have found a work around for this problem. I was using Oracle's ODBC driver, version 8.01.05.00. I switched to using the Microsoft ODBC for Oracle driver, version 2.573.9030.00, and the problem seems to have resolved.

oracle linked server decimal problem

I'm attempting to query a linked server (Oracle) via QA. My query returns th
e expected record set, but the data for the decimal columns is not correct (
only displays zeros to the right of the decimal point). My T-SQL:
SELECT amount
FROM oracle_src...budget
.. yields the result:
.000
.. whereas the equivalent query via MS Access yields
2784.56
The data type for the amount column in Oracle is defined as Numeric(9,3).
Any thoughts? I could pass the whole query process through MS Access to Orac
le, but ... seems like there should be a more elegant solution. Thanks for a
ny suggestions!
- DenisI may have found a work around for this problem. I was using Oracle's ODBC d
river, version 8.01.05.00. I switched to using the Microsoft ODBC for Oracle
driver, version 2.573.9030.00, and the problem seems to have resolved.

oracle linked server decimal problem

I'm attempting to query a linked server (Oracle) via QA. My query returns the expected record set, but the data for the decimal columns is not correct (only displays zeros to the right of the decimal point). My T-SQL
SELECT amoun
FROM oracle_src...budge
... yields the result
..00
... whereas the equivalent query via MS Access yield
2784.5
The data type for the amount column in Oracle is defined as Numeric(9,3)
Any thoughts? I could pass the whole query process through MS Access to Oracle, but ... seems like there should be a more elegant solution. Thanks for any suggestions
- DeniI may have found a work around for this problem. I was using Oracle's ODBC driver, version 8.01.05.00. I switched to using the Microsoft ODBC for Oracle driver, version 2.573.9030.00, and the problem seems to have resolved.