?
Solved

Pass variable as column name in stored procedure

Posted on 2005-04-19
6
Medium Priority
?
252 Views
Last Modified: 2010-03-19
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?
0
Comment
Question by:PsychoDazey
  • 2
  • 2
  • 2
6 Comments
 
LVL 5

Expert Comment

by:obahat
ID: 13816659
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
0
 
LVL 5

Assisted Solution

by:obahat
obahat earned 1800 total points
ID: 13816668
Correction:

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
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13816725
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Accepted Solution

by:
rafrancisco earned 200 total points
ID: 13816734
Don't forget to cast the @user_id to varchar so that you won't get an error, and don't forget to put a space between the @column and @sort after the ORDER BY:

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
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 13816739
thanks, im testing right now.
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 13816801
Thank you both, it worked superbly.  I gave rafrancisico 50 points for his input.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question