troubleshooting Question

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

Avatar of splanton
splantonFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server
5 Comments1 Solution244 ViewsLast Modified:

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
       
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros