asked on

Pass variable as column name in stored procedure

I have the following stored procedure:

CREATE procedure procGetAssignedUserApplications_revised
            (@user_id integer,
            @column varchar(50)='plan_id',
            @sort varchar(4)='ASC')

AS

SELECT created_by_username,
      last_modified_username,
      assigned_to_username,
      application_id,
      application_status_id,
      application_type_id,
      last_email_reminder_date,
      previously_declined,
      previous_declination_text,
      last_name_explanation,
      date_created,
      date_last_modified,
      user_id_created,
      user_id_last_modified,
      effective_date,
      end_date,
      assigned_to_user_id,
      plan_id
FROM vw_GetUserApplications
WHERE assigned_to_user_id=@user_id
ORDER BY @column + '''' + @sort + ''''
GO

I keep getting an error saying:
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

Even though I am trying to sort by a column name...how can I resolve this?

obahat

Try

CREATE procedure procGetAssignedUserApplications_revised
(@user_id integer,
@column varchar(50)='plan_id',
@sort varchar(4)='ASC')

AS

EXEC('SELECT created_by_username,
last_modified_username,
assigned_to_username,
application_id,
application_status_id,
application_type_id,
last_email_reminder_date,
previously_declined,
previous_declination_text,
last_name_explanation,
date_created,
date_last_modified,
user_id_created,
user_id_last_modified,
effective_date,
end_date,
assigned_to_user_id,
plan_id
FROM vw_GetUserApplications
WHERE assigned_to_user_id=@user_id
ORDER BY ' + @column + @sort + ')
GO

SOLUTION

obahat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rafrancisco

Don't forget to cast the @user_id to varchar so that you won't get an error:

CREATE procedure procGetAssignedUserApplications_revised
(@user_id integer,
@column varchar(50)='plan_id',
@sort varchar(4)='ASC')

AS

EXEC('SELECT created_by_username,
last_modified_username,
assigned_to_username,
application_id,
application_status_id,
application_type_id,
last_email_reminder_date,
previously_declined,
previous_declination_text,
last_name_explanation,
date_created,
date_last_modified,
user_id_created,
user_id_last_modified,
effective_date,
end_date,
assigned_to_user_id,
plan_id
FROM vw_GetUserApplications
WHERE assigned_to_user_id=' + CAST(@user_id AS VARCHAR(10)) + '
ORDER BY ' + @column + @sort)
GO

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PsychoDazey

ASKER

thanks, im testing right now.

PsychoDazey

ASKER

Thank you both, it worked superbly. I gave rafrancisico 50 points for his input.