Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Search Queries
Dynamic Search Queries [message #250880] Wed, 11 July 2007 13:29 Go to next message
darshita
Messages: 5
Registered: July 2007
Location: PA, USA
Junior Member
Hello All,

I want to create a dynamic search stored procedure in oracle database. I am very new at oracle. Please, help! below is the example of what I want to accomplish in tsql but I need the help in pl/sql with cursor so muliple rows can be returned back to calling asp.net web.



CREATE PROCEDURE search_orders_1

@orderid int = NULL,

@fromdate datetime = NULL,

@todate datetime = NULL,

@minprice money = NULL,

@maxprice money = NULL,

@custid nchar(5) = NULL,

@custname nvarchar(40) = NULL,

@city nvarchar(15) = NULL,

@region nvarchar(15) = NULL,

@country nvarchar(15) = NULL,

@prodid int = NULL,

@prodname nvarchar(40) = NULL,

@debug bit = 0 AS



DECLARE @sql nvarchar(4000),

@paramlist nvarchar(4000)



SELECT @sql =

'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,

c.CustomerID, c.CompanyName, c.Address, c.City,

c.Region, c.PostalCode, c.Country, c.Phone,

p.ProductID, p.ProductName, p.UnitsInStock,

p.UnitsOnOrder

FROM dbo.Orders o

JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID

JOIN dbo.Customers c ON o.CustomerID = c.CustomerID

JOIN dbo.Products p ON p.ProductID = od.ProductID

WHERE 1 = 1'



IF @orderid IS NOT NULL

SELECT @sql = @sql + ' AND o.OrderID = @xorderid' +

' AND od.OrderID = @xorderid'



IF @fromdate IS NOT NULL

SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate'



IF @todate IS NOT NULL

SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate'



IF @minprice IS NOT NULL

SELECT @sql = @sql + ' AND od.UnitPrice >= @xminprice'



IF @maxprice IS NOT NULL

SELECT @sql = @sql + ' AND od.UnitPrice <= @xmaxprice'



IF @custid IS NOT NULL

SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' +

' AND c.CustomerID = @xcustid'



IF @custname IS NOT NULL

SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%'''



IF @city IS NOT NULL

SELECT @sql = @sql + ' AND c.City = @xcity'



IF @region IS NOT NULL

SELECT @sql = @sql + ' AND c.Region = @xregion'



IF @country IS NOT NULL

SELECT @sql = @sql + ' AND c.Country = @xcountry'



IF @prodid IS NOT NULL

SELECT @sql = @sql + ' AND od.ProductID = @xprodid' +

' AND p.ProductID = @xprodid'



IF @prodname IS NOT NULL

SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%'''



SELECT @sql = @sql + ' ORDER BY o.OrderID'



IF @debug = 1

PRINT @sql



SELECT @paramlist = '@xorderid int,

@xfromdate datetime,

@xtodate datetime,

@xminprice money,

@xmaxprice money,

@xcustid nchar(5),

@xcustname nvarchar(40),

@xcity nvarchar(15),

@xregion nvarchar(15),

@xcountry nvarchar(15),

@xprodid int,

@xprodname nvarchar(40)'



EXEC sp_executesql @sql, @paramlist,

@orderid, @fromdate, @todate, @minprice,

@maxprice, @custid, @custname, @city, @region,

@country, @prodid, @prodname
  • Attachment: tsql.txt
    (Size: 6.29KB, Downloaded 126 times)
Re: Dynamic Search Queries [message #250883 is a reply to message #250880] Wed, 11 July 2007 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Please post what you already tried we are not there to do all your job.
This may help you: PL/SQL User's Guide and Reference

Regards
Michel
Re: Dynamic Search Queries [message #250887 is a reply to message #250883] Wed, 11 July 2007 13:56 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://www.orafaq.com/forum/?t=msg&th=83121/0/

http://asktom.oracle.com/tkyte/ResultSets/


Re: Dynamic Search Queries [message #251083 is a reply to message #250887] Thu, 12 July 2007 06:39 Go to previous messageGo to next message
darshita
Messages: 5
Registered: July 2007
Location: PA, USA
Junior Member
Thank you so much for your help Andrew.
icon12.gif  Re: Dynamic Search Queries [message #251087 is a reply to message #250883] Thu, 12 July 2007 06:42 Go to previous messageGo to next message
darshita
Messages: 5
Registered: July 2007
Location: PA, USA
Junior Member
I was busy reading those tips and tricks for formatting my post and that's why I asked you to do my job

Re: Dynamic Search Queries [message #251099 is a reply to message #251087] Thu, 12 July 2007 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good then next time you'll post a formated question as you now know how to do it.

Regards
Michel
Re: Dynamic Search Queries [message #251237 is a reply to message #251099] Thu, 12 July 2007 13:35 Go to previous message
darshita
Messages: 5
Registered: July 2007
Location: PA, USA
Junior Member
Absolutely! I will do that...
Previous Topic: Stored procedure name length in Oracle
Next Topic: Input tables values created by 1st query to 2nd query
Goto Forum:
  


Current Time: Sun Dec 11 03:59:52 CST 2016

Total time taken to generate the page: 0.07947 seconds