Stored Procedure, not sure how to nest

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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Stored Procedure, not sure how to nest

Author

Topic

lemunk
Starting Member

9 Posts

Posted - 2011-11-22 : 05:43:41

Hi guys,
Kinda new to sql, im trying to get a stored procedure to work.
ive got 2 tables on one database on a server that i need to join with 1 table from a local server.
so basically i got 3 tables needing to be joined.
I get deffered errors.
i think im close just would like a solution to the code i have:


USE [ShaftData]
GO
/****** Object:  StoredProcedure [dbo].[ztester1]    Script Date: 11/22/2011 09:48:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ztester2]
AS
SELECT 
	A.Acct, 
	A.Name, 
	A.Document, 
	A.Part,
	B.Pareto, 
	A.PG,   
	A.Qty, 
	A.Unit, 
	A.[datetime], 
	A.Year_1, 
	A.Month_1 
FROM 
	OPENQUERY (sacbtest, 'SELECT Acct,Name,Document,Part,PG,Qty,Unit,[DateTime], 
	DATEPART(year, dbo.IHeads.DateTime)AS Year_1, DATEPART(Month, dbo.IHeads.DateTime) AS 
    Month_1 FROM AutoPart.Dbo.Iheads INNER JOIN AutoPart.Dbo.Ilines ') A
INNER JOIN 
OPENQUERY(sacbtest, 'SELECT Part,Pareto FROM ShaftData.dbo.NEWPareto') B
ON 
	A.part collate SQL_Latin1_General_CP1_CI_AS = B.part;

any help would be awesome!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 07:29:34

whats the error you're getting?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lemunk
Starting Member

9 Posts

Posted - 2011-11-22 : 08:10:59

deffered, but ive changed the code and now im getting a different error: Invalid column name 'Document'.

[CODE]
USE [ShaftData]
GO
/****** Object: StoredProcedure [dbo].[ztester1] Script Date: 11/22/2011 09:48:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ztester2] /*@acct varchar, @DateTime datetime*/
AS
SELECT iheads_1.Acct,
iheads_1.Name,
iLines_1.Document,
iLines_1.Part,
iLines_1.Qty,
iLines_1.Unit,
dbo.NEWPareto.Pareto,
dbo.NEWPareto.PG,
iheads_1.DateTime
FROM
OPENQUERY(SACBTEST, 'SELECT document,part,qty,unit FROM AutoPart.dbo.iLines') iLines_1
INNER JOIN
OPENQUERY(SACBTEST, 'SELECT acct,name,[datetime] FROM AutoPart.Dbo.iheads') iheads_1
ON
iLines_1.Document collate SQL_Latin1_General_CP1_CI_AS = iheads_1.Document
INNER JOIN
dbo.NEWPareto
ON
iLines_1.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NEWPareto.Part
[\CODE]

lemunk
Starting Member

9 Posts

Posted - 2011-11-22 : 08:27:13

ah nvm ive sorted it now.

this is the corrected code:
[CODE]
USE [ShaftData]
GO
/****** Object: StoredProcedure [dbo].[ztester1] Script Date: 11/22/2011 09:48:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ztester3] /*acct varchar, @DateTime datetime*/
AS
SELECT ih.Acct,
ih.Name,
iL.Document,
iL.Part,
iL.Qty,
iL.Unit,
dbo.NEWPareto.Pareto,
dbo.NEWPareto.PG,
ih.DateTime,
DATEPART(year,ih.DateTime) AS Year_1,
DATEPART(Month, ih.DateTime) AS Month_1
FROM
OPENQUERY(SACBTEST, 'SELECT Document,part,qty,unit FROM AutoPart.dbo.iLines') iL
INNER JOIN
OPENQUERY(SACBTEST, 'SELECT acct,name,[datetime], Document FROM AutoPart.Dbo.iheads') ih
ON
iL.Document collate SQL_Latin1_General_CP1_CI_AS = ih.Document
INNER JOIN
dbo.NEWPareto
ON
iL.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NEWPareto.Part
[/CODE]
the next stage is the where clause that will be there
I need to pass acct and month and year as parameters.
as u can see though i use the datepart fucntion so i dont know how to set there type?
any suggesttions? at the mo i have @acct varchar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources