Avatar of splanton
splanton
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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


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

       
Microsoft SQL Server

Avatar of undefined
Last Comment
splanton

8/22/2022 - Mon
Anthony Perkins

>>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.

Anthony Perkins

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

Anthony Perkins

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
splanton

ASKER
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.