Order By Parameter

Is it possible to use a variable like @.SelectOrderBy with the Order By of a stored procedure? or do I need several SP's?

Select *
FROM Table
Order By @.SelectOrderBy

I want to let a user select from a drop down on a webform the order by column to use.

ThanksYou'd either have seperate stored procedures (or a conditional within one stored procedure), or to implement it like your example above you would have to use dynamic SQL (look in Books Online for proper syntax, mine is iffy):

Declare @.strSQL VARCHAR(8000)
SELECT @.strSQL = "SELECT * FROM table ORDER BY " + @.SelectOrderBy
EXEC @.strSQL

It's not advisable to do it this way however, due to security and efficiencty issues. Stick with the conditional within one proc if it's not too complex!|||the other option is to pull the data back into a dataset and then use a dataview with a sort, passing in the column they selected.

cs

ORDER BY Command in SQL Stored Procedure

How do you use a variable in the ORDER BY command in a sql statement.
I currently have:
****************************************
CREATE Procedure SS_POList
(
@.CompanyID nvarchar(10),
@.PO varchar (20) = '%'
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @.CompanyID
AND
SS_Sendback.EnduserPO Like @.PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
SS_Sendback.PO
GO
*************************************
I changed it to
*************************************
CREATE Procedure SS_POList
(
@.CompanyID nvarchar(10),
@.PO varchar (20) = '%',
@.Order varchar(20)
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @.CompanyID
AND
SS_Sendback.EnduserPO Like @.PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
@.Order
GO

and I receive the following error..
error1008: 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.
How does it know @.order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help.
Thanksas you've discovered, the order by cannot be dynamic. you'd have to use a CASE statement to use a variable here - pseudocode

CASE @.orderBy = 'column1'
ORDER BY column1

that kinda thing. I've seen an FAQ on this somewhere, I'll see if I can dig out the link.|||I'd like to see the FAQ if you can find it. I tried looking but I had no luck.|||try this one for size :

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=344|||Rather than the method suggested in the article:

declare @.OrderBy varchar(20)
select @.Orderby = 'ContactName'
select * from Northwind.dbo.Customers
ORDER BY
 CASE @.OrderBy
 WHEN 'CompanyName' THEN CompanyName
 WHEN 'ContactName' THEN ContactName
 END
 ASC

I suggest this method:

declare @.OrderBy varchar(20)
select @.Orderby = 'ContactName'
select * from Northwind.dbo.Customers
ORDER BY
 CASE WHEN @.OrderBy ='CompanyName' THEN CompanyName END ASC,
 CASE WHEN @.OrderBy ='ContactName' THEN ContactName END ASC

The method in the article requires all of the columns to be of the same datatype (or CAST as the same datatype). The alternate method allows you to use different data types easily, plus use ASC and DESC as needed.

Terri|||Thanks for all you help.
This seems like a lot of work when a simple
ORDER BY @.somevariable
should work but apperently thats not supported by SQL server.
In my searches I also found some suggestions to assign the search string to a variable and execute the string but I think that would be the least desireable way to do this.
Thanks Again.

ORDER BY clause - newbie question

Hi,

Is there any way of passing a variable instead of a hard-coded column name in the ORDER BY clause? E.g.

declare @.OrderCol int
set @.OrderCol = 1 select * from tbl_Box order by @.OrderCol

I know the above code won't run. What I need is be able to determine to sort column at run-time so that instead of writing four different stored procedures with hard-coded order by clauses, I could pass the sort column as an extra parameter to a generic stored procedure. Is that possible at all?

Any help will be appreciated.

Cheers,

Vladislav

Hi Vladislav,

Yes, you can. In your scenario, you would:

declare @.s nvarchar(255),
@.c nvarchar(100)

set @.s = 'select * from tblBox order by '
set @.c = '1' --or 2 or 'BoxNumber' etc.

set @.s = @.s + @.c

exec sp_executesql @.s

Cheers

Rob

|||

Hi Rob,

Thanks a lot. This should certainly help. What I was also looking for is be able to create the following stored procedure

MyDB_sp_GetBoxesByCustomerId [param 1] @.CustomerId int, [param 2] @.SortColumn nvarchar(128)

After some data manipulation, this stored procedure would return a resultset sorted based on the input column name. I would use this stored procedure in my .NET application.

Thanks to your advice, I now know I can build an SQL string and, using sp_exectesql, run it in a .NET program, but I was hoping to find a solution to keep all the 'messy' SQL manipulations inside the stored procedure. Do you think this will be possible?

Once again, thanks your your help.

Cheers,

Vladislav

|||

Hi,

Maybe you can use a construction like:

Select * From Table
Order by Case @.xSort
When 1 Then ColumnName1
When 2 Then ColumnName2
When 3 Then ColumnName3
End

The @.xSort would need to be an input parameter to your procedure

---

Best regards Georg
www.l4ndash.com - Log4net Dashboard / Log4net viewer|||

Thanks alot, Georg. This is certainly a better solution.

Regards,

Vladislav

|||If the possible sort columns are not type-compatible, you will need to do this:

...

order by

case @.xSort when 1 then ColumnName1 end,

case @.xSort when 2 then ColumnName2 end,

case @.xSort when 3 then ColumnName3 end

If you don't do this, the CASE statement will raise an exception the

first time you sort by a column containing a value that cannot be

converted to the highest-precedence type of the three columns.

This version will also avoid unnecessary type conversion in the CASE

statement that could lead to a slower-running query, if an index can't

be used as a result.

Steve Kass

Drew University|||

Drew,

Thanks a bunch. I tried the initial version. As you predicted, I got an exception because my query indeed had a column that could not be converted to the first column. With nothing in MSDN, I was just about to rewrite the stored procedure, when I thought I should check out the forum once more.

Once again, thank a lot.

Cheers,

Vladislav

Oracle, SQL+ and calling a stored proc in a package

I have this code:

declare
return_status number;
return_msg varchar2(255);
variable retval number;
execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
/

I get SP2-0552: Bind variable "RETVAL" not declared.

I have searched Google and the Oracle docs and I cannot find ONE single decent example of calling a stored procedure that's in a package and where that stored procedure has OUT parameters.

HELP!!?!?!

n/m

this worked:

declare
return_status number;
return_msg varchar2(255);
begin
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/Originally posted by nightowlky
I have this code:

declare
return_status number;
return_msg varchar2(255);
variable retval number;
execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
/

I get SP2-0552: Bind variable "RETVAL" not declared.

I have searched Google and the Oracle docs and I cannot find ONE single decent example of calling a stored procedure that's in a package and where that stored procedure has OUT parameters.

HELP!!?!?!

n/m

this worked:

declare
return_status number;
return_msg varchar2(255);
begin
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/
The problem is that you are confusing SQL Plus syntax with PL/SQL syntax. VARIABLE and EXECUTE are SQL Plus commands, so your example should look like one of these:

1) All PL/SQL variables:

declare
return_status number;
return_msg varchar2(255);
retval number; -- no VARIABLE keyword
begin
retval := -- No EXECUTE and no ":"
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/

2) SQL Plus variables:

SQL> variable retval number;
SQL> variable return_status number;
SQL> variable return_msg varchar2(255);
SQL> execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);

3) Combination of SQL Plus and PL/SQL variables:

SQL> variable retval number;

declare
return_status number;
return_msg varchar2(255);
begin
:retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/

The difference is that SQL Plus variables are available to SQL Plus after the PL/SQL block has completed execution, i.e. in examples 2 and 3 you can go on to use :retval again.|||Thanks, Andrew.

But, using all 'variable' statements, I get this:

Wrote file afiedt.buf

1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SQL> run
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SP2-0552: Bind variable "RETURN_MSG" not declared.

All I'm really trying to do is be able to print out the values of return_status and return_msg.

I'm all new to Oracle, SQL+, PL/SQL. I come from a long history of MS SQL Server. Speaking of which, are there tools similar to the MS tools for writing queries, importing data, etc.? Seems all I have installed on this workstation is SQL+ and WinSQL (which chokes on syntax that works in SQL+ ??)|||Originally posted by nightowlky
Thanks, Andrew.

But, using all 'variable' statements, I get this:

Wrote file afiedt.buf

1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SQL> run
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SP2-0552: Bind variable "RETURN_MSG" not declared.

All I'm really trying to do is be able to print out the values of return_status and return_msg.

I'm all new to Oracle, SQL+, PL/SQL. I come from a long history of MS SQL Server. Speaking of which, are there tools similar to the MS tools for writing queries, importing data, etc.? Seems all I have installed on this workstation is SQL+ and WinSQL (which chokes on syntax that works in SQL+ ??)
The problem is that the RUN command treats contents of the buffer as a single command, which this is not. Instead of using RUN, try this:

SQL> @.afiedt.buf

That correctly processes 4 "statements" (3 VARIABLE commands and one EXECUTE command).|||ah...

well...

it gets better:

SQL> @.afiedt.buf

PL/SQL procedure successfully completed.

variable return_status number;
*
ERROR at line 1:
ORA-00900: invalid SQL statement

DOH!|||Originally posted by nightowlky
ah...

well...

it gets better:

SQL> @.afiedt.buf

PL/SQL procedure successfully completed.

variable return_status number;
*
ERROR at line 1:
ORA-00900: invalid SQL statement

DOH!
What does afiedt.buf contain now?|||VARIABLE return_status NUMBER
VARIABLE return_msg VARCHAR2(255)
EXECUTE update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg)
/

I don't believe the whole procedure returns a value (other than the out parameters) so I removed the retval variable.|||Originally posted by nightowlky
VARIABLE return_status NUMBER
VARIABLE return_msg VARCHAR2(255)
EXECUTE update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg)
/

I don't believe the whole procedure returns a value (other than the out parameters) so I removed the retval variable.
You don't need or want that "/" on the last line. Remove that and the error should go away!|||Ah!

The sweet smell of success!

Thank you, Andrew!

Thank you!

:D|||Why dont you try using dbms_output.put_line function to display the output ? The steps are as follows :

-- test1.sql
declare
l_ret_val number;
begin
l_ret_val := call_function();
dbms_output.put_line(l_ret_val);
end;
/

Now at sql prompt execute the following commands :
sql> set serveroutput on
sql> @.test1.sql

Hope this helps ...|||I'll try that, too.

As I said...I'm quite the Oracle newbie and I'm trying to just find stuff that works. And finding examples of some of this stuff is like trying to put your elbow in your ear (can almost get there but not quite) :)|||Originally posted by nightowlky
I'll try that, too.

As I said...I'm quite the Oracle newbie and I'm trying to just find stuff that works. And finding examples of some of this stuff is like trying to put your elbow in your ear (can almost get there but not quite) :)
When using VARIABLE in SQL Plus, you will find the PRINT command handy:

SQL> variable a number
SQL> exec :a := 123

PL/SQL procedure successfully completed.

SQL> print a

A
----
123

Oracle with Reporting Services. "illegal variable name/number"

I'm developing a report with SQL Server Reporting Solutions (obviously ;)) against an Oracle DB and have run into a problem.

This is the code that I'm using for the dataset query that my report is to be based on:

="SELECT * FROM IRMFE_*************_V WHERE " &
IIf(Parameters!Invoice_No.Value(0)<> "ALL", "SUPPLIER_INVOICE_NO IN (:InvNo)", "SUPPLIER_INVOICE_NO LIKE '%'")

The parameter ":InvNo" is a paramter in the report and the user can select multiple values. I also included an "ALL" option manually in the query for the parameter. The query works fine with single or multiple options but if I select "ALL" I get the following error:

"Query execution failed for dataset 'FREIGHT'.
ORA-01036: illegal variable name/number'

There seems to be no problem when I manually run the query (SELECT * FROM IRMFE_*************_V WHERE SUPPLIER_INVOICE_NO LIKE '%')

The reason I used "SUPPLIER_INVOICE_NO LIKE '%'" is because I'll be adding a few more parameters and wanted to avoid any problems with adding "AND" between the conditions.

Any resolution or suggestion on this would be much appreciated guys.
Kev.After 1/2 of bangin my head against the wall I've resolved it. The reason it wasn't picking it up in the first place was that the check should have been:

IIf(Parameters!Invoice_No.Value(0)<> "'ALL'"

instead of

IIf(Parameters!Invoice_No.Value(0)<> "ALL"

I found Chris Hays solution and will just use that though.