Home » SQL & PL/SQL » SQL & PL/SQL » Converting a SQL Server stored procedure to Oracle
Converting a SQL Server stored procedure to Oracle [message #193073] Thu, 14 September 2006 15:11 Go to next message
Eilistraee
Messages: 5
Registered: September 2006
Junior Member
We're using reporting services (which I'm new to) and Oracle Stored Procedures (also new to). We are converting from VB6 and classic ASP to .NET. At first we were going to use SQL Server, but the decision was made that Oracle would be better.

I have dropdown boxes on my page so that the user can select their criteria (ex. color, model, etc.) and then get results back on the matches. At the top of my report I want labels that say Color: Green or whatever their selection is. I don't know of a way to get the ddlbox.selecteditem directly to the report, so I created a stored procedure - it worked pefectly. But now we're switching to Oracle, and I don't know how to get this to work in Oracle.

ALTER PROCEDURE dbo.sp_Carlbls
 
 (
 @colorid int,
 @modelID int 
 )

AS
declare @ModelName char(20), @ColorName char(20)
 if @modelid <> -1
 set @ModelName = ( select model_name from dbo.tablename where model_id = @modelID)
if @modelid = -1 
 set @ModelName = 'ALL'
 if @colorid <> -1 
 set @ColorName = ( select color_name from dbo.tablename color_id = @colorid)
 else 
 set @ColorName = 'ALL'
 
select @ModelName as lblModel, @ColorName as lblColor 
  RETURN


Any help would be very appreciated.

[Updated on: Thu, 14 September 2006 15:13]

Report message to a moderator

Re: Converting a SQL Server stored procedure to Oracle [message #193313 is a reply to message #193073] Fri, 15 September 2006 11:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I'm not 100% shure what the procedure does, but in Oracle it should work like :

create or replace procedure dbo.sp_Carlbls
 
 (
 colorid in number,
 modelID in number,
 lblModel out varchar2(20),
 lblColor out varchar2(20)
 )

AS
begin
 if modelid <> -1 then
     select model_name into lblModel from dbo.tablename where model_id = modelID;
 end if;
 
 if modelid = -1 then
    lblModel = 'ALL'
 end if;
 if colorid <> -1 then
    select color_name into lblColor from dbo.tablename color_id = colorid;
 else 
     set lblColor= 'ALL'
 end if;
end;


( If there acutally IS a user dbo and the tables are in his shema. )
Re: Converting a SQL Server stored procedure to Oracle [message #193314 is a reply to message #193313] Fri, 15 September 2006 11:43 Go to previous messageGo to next message
Eilistraee
Messages: 5
Registered: September 2006
Junior Member
Thanks very much. It returns what I want when I test it. But when I try to use it in my report (or as datasource for a gridciew), I'm told the dataset doesn't return a table. So how do I get the data out?

[Updated on: Fri, 15 September 2006 13:35]

Report message to a moderator

Re: Converting a SQL Server stored procedure to Oracle [message #193637 is a reply to message #193314] Mon, 18 September 2006 11:16 Go to previous message
Eilistraee
Messages: 5
Registered: September 2006
Junior Member
Never mind - I got it!

Thanks for all the help.
Previous Topic: How to retrieve columns by its order
Next Topic: Why don't I get a number in the first row?
Goto Forum:
  


Current Time: Tue Dec 06 13:58:53 CST 2016

Total time taken to generate the page: 0.08831 seconds