0

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

MarceeMarc
  • 1
  • 1

2 Answers2

0

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.

jean
  • 4,159
  • 4
  • 31
  • 52
0

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);
Question3CPO
  • 1,202
  • 4
  • 15
  • 29
  • Many thanks. I thought the very same about altering existing sps, and they are useful for creating certain forms and reports. I have also tried creating custom sps, but wasn't able to produce the results i wanted with one query. I'm not sure what you mean by 'pull the query directly'. Do you mean use sqlcommandtext to write the inline SQL statement? Thanks for your help! – MarceeMarc Dec 12 '13 at 17:21
  • @MarceeMarc I've seen some developers in `C#` and `VB` that will have the query listed in their code, which I am not a fan of, especially if it is a set of data that you may call elsewhere. It can be done, but it's not my preferred method. – Question3CPO Dec 12 '13 at 20:14
  • It's what I've seen in a lot of tutorials, also. I import the sp results set as a dataset and use that (if that makes sense; I'm still getting to grips with terminology and logic!). thanks for the help and responses. – MarceeMarc Dec 12 '13 at 20:52