Query by parameter

[raghu_nv]

I have a table in my SQL-Server database 'Vendor' with 'Vendor_Name' and 'Vendor_Type' as fields.
I want to get a recordset which should contain list of Vendor Names but only for the specified vendor type.

I will supply the vendor type from vb code , i want to do this from a view only But what should be the syntax of view ?

Although this can be done using a stored procedure which works for SQL-Server only but not with oracle.

Plase reply if you have any idea.

[Iouri]

open recordset with SQL
SQL = "select * from Vendor where Vendor_Type = ....."

Iouri Boutchkine
iouri@hotsheet.com

[Cakkie]

You can't pass a parameter to a view. I think you have 3 choises:

1) create a view for every vendor type
2) use stored procedures
3) execute the SQL statement directly on the tables

Option 1 I would forget if I were you, this way if you have 50 types of vendors, you have 50 views, very hard to maintain on the server
Option 2 is good for SQL Server, but can't be used on Oracle
Option 3 is the most flexible, but this leaves the select statements at the client side, so in your VB client, which can make it very hard to read and maintain

If you can't go for option 2, go for option 3

Tom Cannaerts
slisse@planetinternet.be

The best way to escape a problem, is to solve it.

[raghu_nv]

Thanks for the suggetions.

1.Creating a view for each vendor type is not possible because there are number of vendor types,
increased or decreased while updations.

2.Using a stored procedure will work for SQL Server (and I think SyBase also ?) but not for oracle.

3.I can't use SELECT Statement directly on tables (as Cakkie and Iouri suggested) because of security reasons where every user connected to database server will make transaction through a stored procedure or an earlier created view.

I got some tips on getting recorset from oracle stored procedures. Please check out these links if you are interested.
http://support.microsoft.com/support/kb/articles/Q229/9/19.asp

http://www.actionjackson.com/articles/20000127/