returning values from a stored procedure

i have a vb6 app which accesses a sql db. i'm using stored procedures to retun valus and recordsets. my question is can i return both a full recordset from one table and a single value from another table with in one stored procedure?.

example:
i have tblcustomers and tblitems, i want to return a full recordset from tblcustomers for a specific customer and i want to retun the item price for a selected item.

can retrive both values in one stored procedure or do i need to create on stored procedure for each.

Thank You,
ThomasOriginally posted by thomas eicondev
i have a vb6 app which accesses a sql db. i'm using stored procedures to retun valus and recordsets. my question is can i return both a full recordset from one table and a single value from another table with in one stored procedure?.

example:
i have tblcustomers and tblitems, i want to return a full recordset from tblcustomers for a specific customer and i want to retun the item price for a selected item.

can retrive both values in one stored procedure or do i need to create on stored procedure for each.

Thank You,
Thomas

If it is OK with you to return two record sets one for tblcustomers table and one for the price selected from tblitems, yes is possible.
In this case the return set for the price will be a table with one field and one record...|||Originally posted by ioana
If it is OK with you to return two record sets one for tblcustomers table and one for the price selected from tblitems, yes is possible.
In this case the return set for the price will be a table with one field and one record...

Also, you can use UNION in stored procedure - just add special field where you can set from what table row is.