Yes, I have done this a lot (lately) but how do I get that result into another SP?
Example:
SP 1:
Lets say I have a Customer table and I pass the parameter to filter for just City critera = @Enter_City (I'll enter Chicago when I run it)

SP 2: I want to see all orders by customers in Chicago who have ordered part number 1234. So I make a SP that uses the Orders table and uses SP1 to which will give me Chicago (or whatever I enter)customers. Then I filter the Orders table for part = @Enter_Part (and I type 1234)

This is overly simplictic, but it is the clearest way I can think of right now to explain what it is I commonly do in Access and want to now do in SQL.

You can't do the same thing in SQL Server. There is no such thing as a "...query that takes a parameter...". There are lots of things (almost all bad :D) you can do in Access that you can't do exactly the same way in SQL Server.

There is no such thing as a "Query" in SQL Server. We refer to queries (lowercase) as being the resultset of a SELECT statement, or the act that we do when we execute such a statement in a tool like Query Analyzer. Instead of Queries which are stored in the database as in Access, SQL Server has Views and Stored Procedures.

Stored procedures may take parameters and may return resultsets, but they may just as well have no parameters and return nothing at all.

Views are virtual tables - they act just like a table which contains rows made up of columns. You select the rows and columns from the view in the same way you would select from a table. You JOIN a View to other Views and/or tables.

The rough equivalent of what you are looking for is that you would pass parameters to a Stored procedure which would execute a SELECT statement returning a resultset. This resultset would be constructed by JOINing your tables and views together as appropriate and then using your parameters as expressions in the WHERE clause to select the appropriate rows from the JOINs of your various tables and views. You might also use your parameters to direct the flow of execution in the Stored Procedure, depending on your requirements.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff is right. Access makes us lazy.
Create the two stored procedures with IN parameters.
Create two combobox controls. Set one to show all cities, set the other to show all part numbers
Have the user select values from both comboboxes and have them vlick a button and run your stored procedure that returns the resultwet that you are looking for.

I think you are trying to do this thinking too much like Access. Do the joins as Jeff suggested and create two IN parameters and use them in the WHERE clause.





Sal

That is, create one stored procedure that takes two parameters and use them both in the SELECT statement.

So, in your earlier post where you wanted to select orders from Chicago for part number 1234, you'd construct a single stored procedure with those two values as parameters, and which contained a SELECT statement which JOINed your Order_Detail to your Order_Header and then JOINed to your Customer table. The WHERE clause in this SELECT statement selects from the Customer table where the Customer.city column equals the City parameter and the Order_Detail.Partnumber column equals the Part Number parameter...


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Access makes you wrong. (at least from a relational point of view).

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

If you post a little more info on what you are trying to do, and the tables you are working with, perhaps we can help you get to where you want to go, without using any canine assistance. :)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Access makes you AutoNumber happy! Way wrong!




Sal