Create Query with Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create Query with Table

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2008-08-20 : 16:59:57

I need to write a stored procedure.

One table contains Serial_Number and WorkOrders, and four (4) other tables contain the records for the parts by Serial_Number.

I need to create a query that allows me to search for parts based on a WorkOrder.

I need to pull all Serial_Numbers for a given WorkOrder, then search the other tables for all instances of those Serial_Numbers, Testers, TestDates, and Status.

What is the best way to create a Stored Procedure like this?

EXEC sp_AllByWorkOrder '{0}'

(Using SQL Server 2000 Enterprise and Visual Studio)

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2008-08-20 : 17:09:12

Here is an example of what I am after, but I am not great at SQL Queries, so this only to get the point across:

CREATE PROCEDURE sp_AllByWorkOrder(@WO nvarchar(10)) AS

SELECT
  Serial_Number, Tester, TestDate, Status
FROM
  dbo.Machines, dbo.Assembly, dbo.Shipping
WHERE
  Serial_Number IN
  (
   SELECT Serial_Number
   FROM dbo.Orders
   WHERE WorkOrder=@WO
  )

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 03:53:47

i think your procedure should be something like this

CREATE PROCEDURE WorkOrderPartsGet
@WorkOrderID int
AS

SELECT m.WorkOrderID,m.SerialNo,t1.fields....,t2.fields...,t3.fields..,t4.fields
FROM MainTable m
LEFT JOIN Table1 t1
ON t1.SerialNo=m.SerialNo
LEFT JOIN Table2 t2
ON t2.SerialNo=m.SerialNo
LEFT JOIN Table3 t3
ON t3.SerialNo=m.SerialNo
LEFT JOIN Table4 t4
ON t4.SerialNo=m.SerialNo
WHERE m.WorkOrderID=@WorkOrderID

jp2code
Posting Yak Master

175 Posts

Posted - 2008-08-21 : 09:31:43

Thanks visakh16.

I wound up learning how to create a table on the fly:

> SELECT Serial_Number
> INTO #t
> FROM dbo.MainTable
> WHERE ([WorkOrder_Number]=@WO)
> ORDER BY Serial_Number
>

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 09:34:50

quote:
Originally posted by jp2code

Thanks visakh16.

I wound up learning how to create a table on the fly:

> SELECT Serial_Number
> INTO #t
> FROM dbo.MainTable
> WHERE ([WorkOrder_Number]=@WO)
> ORDER BY Serial_Number
>

Avoid Sears Home Improvement

You dont need a temp table. you can directly join main table onto others using SerialNo and retrive matching fields as shown earlier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources