Relationships between stored procs?

Question

I'll start by saying hello! This forum has been a great help to me over the past few months, but have only now joined and asking my first question. I'm working with the Northwind database in SQL Server 2008 r2 to build a vb.net application. I've been wrecking my head for a week trying to figure out how to make an order/invoice form. I can get the information I need to display using separate stored procs (GetCustInfo, GetOrderInfo, GetProductInfo, or something like that), but I'm having trouble figuring out how to display them on the form. When I select all the info I need in one sp (as in the Invoice view which comes built in the db), I get 2155 rows, which is the number of items which have been ordered in the company history. What I want to do is display this information, but navigate by OrderID (which would give me 830 rows, each with a certain number of products related to the OrderID). So I'm thinking I need different stored procs related which can be related in some way. I'd really appreciate any help that can be given on this.

Many thanks in advance.

p.s. I have screenshots of the Northwind sample app which shipped/ships with Access, which is really what I'm trying to recreate in SQL Server. Unfortunately, no code! MM

Answer 1

Some options (with contrived examples):

You can ALTER existing stored procedures to get what you want (not recommended if you want to use the existing procedures for other queries).

ALTER PROCEDURE usp_ExistingProcedure
AS
BEGIN

SELECT t1.Value
, t2.Value
-- Supose that this was the addition we made to an existing stored procedure
, t2.ValueTwo
FROM TableOne t1
INNER JOIN TableTwo t2 ON t1.ID = t2.ID

END

You can CREATE new stored procedures for your queries; in the above example, it would be a create procedure with a new name.

You may be able to create a VIEW to obtain what you need - this will operate a little differently.

CREATE VIEW uv_ApplicationView
AS

SELECT t1.Value
    , t2.Value
    , t2.ValueTwo
    FROM TableOne t1
    INNER JOIN TableTwo t2 ON t1.ID = t2.ID

You can pull the query directly from the VB application, though if you want to reuse it for something else, I wouldn't recommend this approach.

//  A re-usable approach calling a stored procedure
SqlCommand myQuery = new SqlCommand("EXECUTE usp_myQuery", sqlConn);

//  A query directly in the C# code:
string msQuery = "SELECT t1.Value, t2.Value, t2.ValueTwo FROM TableOne t1 INNER JOIN TableTwo t2 ON t1.ID = t2.ID"
// Later ...
SqlCommand myQuery = new SqlCommand(msQuery, sqlConn);

Answer 2

Yes you can achieve it by many ways and SP is one. Just create a SP to select that related products passing OrderId as a input parameter.