"Order By" Using a Parameter for the Column Name

Order By using a parameter for the column name

You should be able to do something like this:

SELECT *
FROM
    TableName
WHERE
    (Forename LIKE '%' + @SearchValue + '%') OR
    (Surname LIKE '%' + @SearchValue + '%') OR
    (@SearchValue = 'ALL')
ORDER BY 
    CASE @OrderByColumn
    WHEN 1 THEN Forename
    WHEN 2 THEN Surname
    END;

• Assign 1 to @OrderByColumn to sort on Forename.
• Assign 2 to sort on Surname.
• Etc... you can expand this scheme to arbitrary number of columns.

---

Be careful about performance though. These kinds of constructs may interfere with query optimizer's ability to find an optimal execution plan. For example, even if Forename is covered by index, query may still require the full sort instead of just traversing the index in order.

If that is the case, and you can't live with the performance implications, it may be necessary to have a separate version of the query for each possible sort order, complicating things considerably client-side.

How to order by a specific column a MySQL query with Java and Hibernate?

You can only pass values as parameters to a query. Not column or field names. That would make it impossible for the database to know which columns are actually used in the query, and thus make it impossible to prepare the execution plan.

So your solution using concatenation is the only one. Just make sure the column doesn't come from the user. Or if it comes from the user, that it's a valid column name and that the user is allowed to use it.

How to pass parameter as column name in stored procedure in my example

You would have to use dynamic sql for something like this. It would be a good idea to whitelist the value of @columnsname against sys.columns or information_schema.columns as well.

Use sp_executesql to continue to keep @columnsvalue as parameter, and not concatenate it to the SQL you will be executing.

create procedure ProcedureName1 (
   @columnsname sysname --varchar(50) 
 , @columnsvalue varchar(50)
) as
begin
declare @sql nvarchar(max), @column_name sysname;
/* make sure the @columnsname is a valid column name */
set @column_name = (
  select c.name
  from sys.columns c
  where c.object_id = object_id(N'Tabl1') 
    and c.name = @columnsname
  );
set @sql = 'with cte as (
    select 
        ID
      , names
      , null as address
      , work
      , note
    from Tabl1
    where '+@column_name+' like @columnsvalue
    union all
    select 
        t2.ID
      , t2.name
      , t2.address
      , null
      , null as tt
    from Tabl2 as t2
    left join Tabl1 as t1 on t2.ID = t1.ID
    where '+@column_name+' like @columnsvalue
    )
  select *
  from cet
  order by 
      id
    , note desc
    , address;'
exec sp_executesql @sql, N'@columnsvalue varchar(50)', @columnsvalue
end;
go

Reference:

• The curse and blessings of dynamic SQL - Erland Sommarskog
• sp_executesql

SQL stored procedure passing parameter into order by

Only by being slightly silly:

CREATE PROCEDURE [dbo].[TopVRM]
@orderby varchar(255)
AS
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto
ORDER by CASE WHEN @orderby='ASC' THEN Pareto END,
         CASE WHEN @orderby='DESC' THEN Pareto END DESC

You don't strictly need to put the second sort condition in a CASE expression at all(*), and if Pareto is numeric, you may decide to just do CASE WHEN @orderby='ASC' THEN 1 ELSE -1 END * Pareto

(*) The second sort condition only has an effect when the first sort condition considers two rows to be equal. This is either when both rows have the same Pareto value (so the reverse sort would also consider them equal), of because the first CASE expression is returning NULLs (so @orderby isn't 'ASC', so we want to perform the DESC sort.

---

You might also want to consider retrieving both result sets in one go, rather than doing two calls:

CREATE PROCEDURE [dbo].[TopVRM]
@orderby varchar(255)
AS

SELECT * FROM (
    SELECT
       *,
       ROW_NUMBER() OVER (ORDER BY Pareto) as rn1,
       ROW_NUMBER() OVER (ORDER BY Pareto DESC) as rn2
    FROM (
        SELECT Peroid1.Pareto
        FROM dbo.Peroid1
        GROUP by Pareto
    ) t
) t2
WHERE rn1 between 1 and 10 or rn2 between 1 and 10
ORDER BY rn1

This will give you the top 10 and the bottom 10, in order from top to bottom. But if there are less than 20 results in total, you won't get duplicates, unlike your current plan.

When passing column name as parameter the query is showing incorrect output

Long story short: what you are trying to do is not possible; you need to fix your approach.

When you declare variable @ColumnName it is not interpreted as a column name; it is always interpreted as a data value - in your case, it is the same as 'EFFECTIVE_DATE' string literal. Effectively, your query works as if it were written as follows:

select * into #temphdrid 
from  EMP_HEADER a 
where 
EMPLOYEE_ID in ('1111','2222') and  
HEADER_ID=(select  max(HEADER_ID) from EMP_HEADER b where 
a.HEADER_ID=b.HEADER_ID
and 'EFFECTIVE_DATE'=(select MAX('EFFECTIVE_DATE') from EMP_HEADER c
where b.EMPLOYEE_ID=c.EMPLOYEE_ID and YEAR(FIN_START_DATE)=2016)
and YEAR(FIN_START_DATE)=2016)
and YEAR(FIN_START_DATE)=2016

This is syntactically correct, but it makes no sense, because MAX('EFFECTIVE_DATE') is always equal to 'EFFECTIVE_DATE'.

An alternative approach would be providing some indication for what column name needs to be selected, and using a case expression for the comparison:

declare @ColIdx tinyint
set @ColIdx=1
...
where 
a.HEADER_ID=b.HEADER_ID
and (case @ColInd when 1 then EFFECTIVE_DATE when 2 then END_DATE else null end)=(select MAX(case @ColInd when 1 then EFFECTIVE_DATE when 2 then END_DATE else null end) from

How to pass a string of column name as a parameter into a CREATE TABLE FUNCTION in BigQuery

For details see:
https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions

A work-around can be to use a case statement to select the desired column. If any column is needed, please use the solution of Mikhail Berlyant.

Create or replace table function   Test.HL(fieldName   string,parameter ANY TYPE)
as
(
SELECT *
From (   select "1" as tmp, 2 as passed_qa) # generate dummy table
Where case 
    when fieldName="passed_qa" then format("%t",passed_qa)
    when fieldName="tmp" then format("%t",tmp)
    else ERROR(concat('column ',fieldName,' not found')) end = parameter
)

How to parameterize column names in a pg query

Plpgsql could be a possible solution.
Consider the following function:

CREATE OR REPLACE FUNCTION run_prepared_on_foobar(q text) RETURNS foobar AS $$
DECLARE
    retval foobar;
BEGIN
    EXECUTE q INTO retval;
    RETURN retval;
END $$ LANGUAGE plpgsql;

Once this is in place, you can then call it by passing any prepared statement:

SELECT * FROM run_prepared_on_foobar('SELECT * FROM foobar WHERE '||$1::text||'='||$2::int)

This is extremely poor in performance since the planner will not be able to peek inside what is going on in the function. All estimates will be random.

Shenanigans of plpgsql aside, a better approach would be to have extra code to switch over different queries depending on external conditions.

---

Related Topics