Calling a Stored Proc from a VIEW - do i have to and if so how?

splanton
splanton used Ask the Experts™
on

I am using a stored procedure as outlined below.

I need to make this output available to a VIEW

I am assuming that I need to use a SP for this type of query and that my SQL is OK (it may be pretty verbose). That being the case how do I make the output available to a VIEW?

	DECLARE @InvoicingData TABLE (
		[WasteOrderDetailId] [int],
		[ExtAccountRef] [varchar](8),
		[ActionDate] [datetime],
		[Notes1] [varchar](60),
		[Notes2] [varchar](60),
		[Notes3] [varchar](60),
		[TakenBy] [varchar](60),
		[CustOrderRef] [varchar](60),
		[Description] [varchar](200),
		[QTY] [int],
		[NetSales] [numeric](8,2),
		[NominalCode] [varchar](8),
		[TaxCode] [int],
		[Department] [int])


	INSERT INTO @InvoicingData(
			[WasteOrderDetailId],
			[ExtAccountRef],
			[ActionDate],
			[Notes1],
			[Notes2],
			[Notes3],
			[TakenBy],
			[CustOrderRef],
			[Description],
			[QTY],
			[NetSales],
			[NominalCode],
			[TaxCode],
			[Department])
	SELECT	WasteOrderDetail.WasteOrderDetailId,
			WasteOrderDetail.ExtAccountRef,
			WasteOrderDetail.ActionDate,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			'Job Ref. ' + WasteOrderDetail.JobRef + ' - ' + ContainerSize.Description + ' ' + Container.Description + ' for ' + WasteType.Description +'. Notes: ' + isnull(WasteOrderDetail.InvoiceNotes,'n/a'),
			1,
			WasteOrderDetail.SaleAdditionalCharge,
			NULL,
			NULL,
			NULL
	FROM	WasteOrderDetail INNER JOIN
			Container ON WasteOrderDetail.ContainerId = Container.ContainerID INNER JOIN
			ContainerSize ON WasteOrderDetail.ContainerSizeId = ContainerSize.ContainerSizeId INNER JOIN
			WasteType ON WasteOrderDetail.WasteTypeId = WasteType.WasteTypeId
	WHERE	(WasteScheduleId IS NULL) AND	
			(MovementRef IS NOT NULL) AND
			(SaleAdditionalCharge IS NOT NULL)
			
	INSERT INTO @InvoicingData(
			[WasteOrderDetailId],
			[ExtAccountRef],
			[ActionDate],
			[Notes1],
			[Notes2],
			[Notes3],
			[TakenBy],
			[CustOrderRef],
			[Description],
			[QTY],
			[NetSales],
			[NominalCode],
			[TaxCode],
			[Department])
	SELECT	WasteOrderDetail.WasteOrderDetailId,
			WasteOrderDetail.ExtAccountRef,
			WasteOrderDetail.ActionDate,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			'Job Ref. ' + WasteOrderDetail.JobRef + ' - ' + ContainerSize.Description + ' ' + Container.Description + ' for ' + WasteType.Description +'. Notes: ' + isnull(WasteOrderDetail.InvoiceNotes,'n/a'),
			1,
			WasteOrderDetail.SaleHaulage,
			NULL,
			NULL,
			NULL
	FROM	WasteOrderDetail INNER JOIN
			Container ON WasteOrderDetail.ContainerId = Container.ContainerID INNER JOIN
			ContainerSize ON WasteOrderDetail.ContainerSizeId = ContainerSize.ContainerSizeId INNER JOIN
			WasteType ON WasteOrderDetail.WasteTypeId = WasteType.WasteTypeId
	WHERE	(WasteScheduleId IS NULL) AND	
			(MovementRef IS NOT NULL) AND
			(SaleHaulage IS NOT NULL) AND
			(WeighbridgeWeight IS NULL)		
			
	INSERT INTO @InvoicingData(
			[WasteOrderDetailId],
			[ExtAccountRef],
			[ActionDate],
			[Notes1],
			[Notes2],
			[Notes3],
			[TakenBy],
			[CustOrderRef],
			[Description],
			[QTY],
			[NetSales],
			[NominalCode],
			[TaxCode],
			[Department])
	SELECT	WasteOrderDetail.WasteOrderDetailId,
			WasteOrderDetail.ExtAccountRef,
			WasteOrderDetail.ActionDate,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			'Job Ref. ' + WasteOrderDetail.JobRef + ' - ' + ContainerSize.Description + ' ' + Container.Description + ' for ' + WasteType.Description +'. Notes: ' + isnull(WasteOrderDetail.InvoiceNotes,'n/a'),
			1,
			WasteOrderDetail.SaleHaulage + (WasteOrderDetail.SaleTonnage * WeighbridgeWeight),
			NULL,
			NULL,
			NULL
	FROM	WasteOrderDetail INNER JOIN
			Container ON WasteOrderDetail.ContainerId = Container.ContainerID INNER JOIN
			ContainerSize ON WasteOrderDetail.ContainerSizeId = ContainerSize.ContainerSizeId INNER JOIN
			WasteType ON WasteOrderDetail.WasteTypeId = WasteType.WasteTypeId
	WHERE	(WasteScheduleId IS NULL) AND	
			(MovementRef IS NOT NULL) AND
			(SaleHaulage IS NOT NULL) AND
			(WeighbridgeWeight > IncludedTonnage)		
	INSERT INTO @InvoicingData(
			[WasteOrderDetailId],
			[ExtAccountRef],
			[ActionDate],
			[Notes1],
			[Notes2],
			[Notes3],
			[TakenBy],
			[CustOrderRef],
			[Description],
			[QTY],
			[NetSales],
			[NominalCode],
			[TaxCode],
			[Department])
	SELECT	WasteOrderDetail.WasteOrderDetailId,
			WasteOrderDetail.ExtAccountRef,
			WasteOrderDetail.ActionDate,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			'Job Ref. ' + WasteOrderDetail.JobRef + ' - ' + ContainerSize.Description + ' ' + Container.Description + ' for ' + WasteType.Description +'. Notes: ' + isnull(WasteOrderDetail.InvoiceNotes,'n/a'),
			1,
			WasteOrderDetail.SaleHaulage,
			NULL,
			NULL,
			NULL
	FROM	WasteOrderDetail INNER JOIN
			Container ON WasteOrderDetail.ContainerId = Container.ContainerID INNER JOIN
			ContainerSize ON WasteOrderDetail.ContainerSizeId = ContainerSize.ContainerSizeId INNER JOIN
			WasteType ON WasteOrderDetail.WasteTypeId = WasteType.WasteTypeId
	WHERE	(WasteScheduleId IS NULL) AND	
			(MovementRef IS NOT NULL) AND
			(SaleHaulage IS NOT NULL) AND
			(WeighbridgeWeight < IncludedTonnage)			
							
select * from @InvoicingData Order By wasteOrderDetailId

Open in new window

       
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
>>That being the case how do I make the output available to a VIEW?<<
You cannot.  Unless you can convert that Stored Procedure to a UDF you are SOL.

Top Expert 2012

Commented:
In other words you would have to convert your Stored Procedure as follows (I am sure it can be optimized):
CREATE FUNCTION dbo.udf_YourUDFNameGoesHere ()

RETURNS @InvoicingData TABLE (
	[WasteOrderDetailId] [int],
	[ExtAccountRef] [varchar](8),
	[ActionDate] [datetime],
	[Notes1] [varchar](60),
	[Notes2] [varchar](60),
	[Notes3] [varchar](60),
	[TakenBy] [varchar](60),
	[CustOrderRef] [varchar](60),
	[Description] [varchar](200),
	[QTY] [int],
	[NetSales] [numeric](8,2),
	[NominalCode] [varchar](8),
	[TaxCode] [int],
	[Department] [int])

AS

BEGIN

INSERT INTO @InvoicingData(
		[WasteOrderDetailId],
		[ExtAccountRef],
		[ActionDate],
		[Notes1],
		[Notes2],
		[Notes3],
		[TakenBy],
		[CustOrderRef],
		[Description],
		[QTY],
		[NetSales],
		[NominalCode],
		[TaxCode],
		[Department])
SELECT	WasteOrderDetail.WasteOrderDetailId,
		WasteOrderDetail.ExtAccountRef,
		WasteOrderDetail.ActionDate,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		'Job Ref. ' + WasteOrderDetail.JobRef + ' - ' + ContainerSize.Description + ' ' + Container.Description + ' for ' + WasteType.Description +'. Notes: ' + isnull(WasteOrderDetail.InvoiceNotes,'n/a'),
		1,
		WasteOrderDetail.SaleAdditionalCharge,
		NULL,
		NULL,
		NULL
FROM	WasteOrderDetail INNER JOIN
		Container ON WasteOrderDetail.ContainerId = Container.ContainerID INNER JOIN
		ContainerSize ON WasteOrderDetail.ContainerSizeId = ContainerSize.ContainerSizeId INNER JOIN
		WasteType ON WasteOrderDetail.WasteTypeId = WasteType.WasteTypeId
WHERE	(WasteScheduleId IS NULL) AND	
		(MovementRef IS NOT NULL) AND
		(SaleAdditionalCharge IS NOT NULL)
		
INSERT INTO @InvoicingData(
		[WasteOrderDetailId],
		[ExtAccountRef],
		[ActionDate],
		[Notes1],
		[Notes2],
		[Notes3],
		[TakenBy],
		[CustOrderRef],
		[Description],
		[QTY],
		[NetSales],
		[NominalCode],
		[TaxCode],
		[Department])
SELECT	WasteOrderDetail.WasteOrderDetailId,
		WasteOrderDetail.ExtAccountRef,
		WasteOrderDetail.ActionDate,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		'Job Ref. ' + WasteOrderDetail.JobRef + ' - ' + ContainerSize.Description + ' ' + Container.Description + ' for ' + WasteType.Description +'. Notes: ' + isnull(WasteOrderDetail.InvoiceNotes,'n/a'),
		1,
		WasteOrderDetail.SaleHaulage,
		NULL,
		NULL,
		NULL
FROM	WasteOrderDetail INNER JOIN
		Container ON WasteOrderDetail.ContainerId = Container.ContainerID INNER JOIN
		ContainerSize ON WasteOrderDetail.ContainerSizeId = ContainerSize.ContainerSizeId INNER JOIN
		WasteType ON WasteOrderDetail.WasteTypeId = WasteType.WasteTypeId
WHERE	(WasteScheduleId IS NULL) AND	
		(MovementRef IS NOT NULL) AND
		(SaleHaulage IS NOT NULL) AND
		(WeighbridgeWeight IS NULL)		
		
INSERT INTO @InvoicingData(
		[WasteOrderDetailId],
		[ExtAccountRef],
		[ActionDate],
		[Notes1],
		[Notes2],
		[Notes3],
		[TakenBy],
		[CustOrderRef],
		[Description],
		[QTY],
		[NetSales],
		[NominalCode],
		[TaxCode],
		[Department])
SELECT	WasteOrderDetail.WasteOrderDetailId,
		WasteOrderDetail.ExtAccountRef,
		WasteOrderDetail.ActionDate,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		'Job Ref. ' + WasteOrderDetail.JobRef + ' - ' + ContainerSize.Description + ' ' + Container.Description + ' for ' + WasteType.Description +'. Notes: ' + isnull(WasteOrderDetail.InvoiceNotes,'n/a'),
		1,
		WasteOrderDetail.SaleHaulage + (WasteOrderDetail.SaleTonnage * WeighbridgeWeight),
		NULL,
		NULL,
		NULL
FROM	WasteOrderDetail INNER JOIN
		Container ON WasteOrderDetail.ContainerId = Container.ContainerID INNER JOIN
		ContainerSize ON WasteOrderDetail.ContainerSizeId = ContainerSize.ContainerSizeId INNER JOIN
		WasteType ON WasteOrderDetail.WasteTypeId = WasteType.WasteTypeId
WHERE	(WasteScheduleId IS NULL) AND	
		(MovementRef IS NOT NULL) AND
		(SaleHaulage IS NOT NULL) AND
		(WeighbridgeWeight > IncludedTonnage)		

INSERT INTO @InvoicingData(
		[WasteOrderDetailId],
		[ExtAccountRef],
		[ActionDate],
		[Notes1],
		[Notes2],
		[Notes3],
		[TakenBy],
		[CustOrderRef],
		[Description],
		[QTY],
		[NetSales],
		[NominalCode],
		[TaxCode],
		[Department])
SELECT	WasteOrderDetail.WasteOrderDetailId,
		WasteOrderDetail.ExtAccountRef,
		WasteOrderDetail.ActionDate,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		'Job Ref. ' + WasteOrderDetail.JobRef + ' - ' + ContainerSize.Description + ' ' + Container.Description + ' for ' + WasteType.Description +'. Notes: ' + isnull(WasteOrderDetail.InvoiceNotes,'n/a'),
		1,
		WasteOrderDetail.SaleHaulage,
		NULL,
		NULL,
		NULL
FROM	WasteOrderDetail INNER JOIN
		Container ON WasteOrderDetail.ContainerId = Container.ContainerID INNER JOIN
		ContainerSize ON WasteOrderDetail.ContainerSizeId = ContainerSize.ContainerSizeId INNER JOIN
		WasteType ON WasteOrderDetail.WasteTypeId = WasteType.WasteTypeId
WHERE	(WasteScheduleId IS NULL) AND	
		(MovementRef IS NOT NULL) AND
		(SaleHaulage IS NOT NULL) AND
		(WeighbridgeWeight < IncludedTonnage)			

RETURN 

END

Open in new window

Top Expert 2012

Commented:
Your VIEW would then become as simple as shown below.  Notice that I have not added an ORDER BY clause in either the UDF or the VIEW.  That is because you cannot have an ORDER BY clause without a TOP clause in either case.  Certainly you will no doubt here from some newbies who will misguidedly suggest that you add TOP 100 PERCENT ... ORDER BY.  Don't be fooled:
1.  Adding TOP 100 PERCENT ... ORDER BY does not sort the results.
2.  Even if that kludge did work, in my view, adding ORDER BY in a VIEW defeats the whole point of using a VIEW in the first place.
CREATE VIEW vw_YourViewNameGoesHere

AS

SELECT	WasteOrderDetailId,
	ExtAccountRef,
	ActionDate,
	Notes1,
	Notes2,
	Notes3,
	TakenBy,
	CustOrderRef,
	Description,
	QTY,
	NetSales,
	NominalCode,
	TaxCode,
	Department
FROM	dbo.udf_YourUDFNameGoesHere

Open in new window

Top Expert 2012
Commented:
This:
FROM      dbo.udf_YourUDFNameGoesHere

Should have been:
FROM      dbo.udf_YourUDFNameGoesHere()

Author

Commented:
WOW! A seriously concise, well explained and well presented answer. Thank you very much for this.
Not only has it answered this specific question but it has also opened up a whole new world of UDF's to me.

Thank you again.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial