none
Converting a View to a Stored Proc

    Question

  • Hello,

    I'm rather rusty on SQL - it's been quite a few years since I have been writing SQL.  I have a View that brings together a large amount of data and is difficult to query.  Deleting the View and turning it into a Stored Proc seems the most efficient way of doing this.  

    The problem is, that I'm stuck on this Stored Proc.  I've taken the View and created a Stored Proc, but the Stored proc doesn't search as it should - it just runs for hours.

    I need it to search the 'WorderKey' field.  Here is the code, any help is greatly appreciated.

    USE [Labels]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_OuterCaseLabelJobsDetail]    Script Date: 07/07/2014 10:49:17 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[sp_OuterCaseLabelJobsDetail]
    	-- Add the parameters for the stored procedure here
    		@Worderkey as varchar(50) = [wo.WORDERKey]
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	SELECT	wo.JobNumber
    		,wo.DateRequired
    		,wo.PartFileItemID
    		,wo.MfgDept
    		,wo.LabelCopies
    		,wo.JobExpiryDate
    		,wo.Batch
    		,wo.JobJulianDate
    		,wo.WORDERKey
    		,dbo.ProductMasterView.[Description]
    		,dbo.ProductMasterView.PackFormat
    		,dbo.ProductMasterView.IngDec
    		,dbo.ProductMasterView.BarCodeNumber
    		,dbo.ProductMasterView.CustomerProductCode
    		,dbo.ProductMasterView.CriticalInstruction
    		,dbo.ProductMasterView.StorageInstruction
    		,dbo.ProductMasterView.MixedCaseTitle
    		,dbo.ProductMasterView.MixedCode1
    		,dbo.ProductMasterView.MixedCode2
    		,dbo.ProductMasterView.MixedCode3
    		,dbo.ProductMasterView.MixedCode4
    		,dbo.ProductMasterView.MixedDescription1
    		,dbo.ProductMasterView.MixedDescription2
    		,dbo.ProductMasterView.MixedDescription3
    		,dbo.ProductMasterView.MixedDescription4
    		,dbo.ProductMasterView.MixedIngDec1
    		,dbo.ProductMasterView.MixedIngDec2
    		,dbo.ProductMasterView.MixedIngDec3
    		,dbo.ProductMasterView.MixedIngDec4
    		,dbo.ProductMasterView.CookingInstruction
    		,dbo.ProductMasterView.LogoFilename
    		,dbo.ProductMasterView.ProductExpiryRule
    		,dbo.ProductMasterView.GS1_AppID
    		,dbo.ProductMasterView.LabelTemplateID
    		,CASE 
    			WHEN ProductMasterView.PartFileItemID IS NULL THEN 'N' ELSE 'Y' END 
    			AS LabelDefExists
    		,dbo.ProductMasterView.[FileName]
    		,dbo.ProductMasterView.DateFormatString
    		,dbo.ProductMasterView.DateRounding
    		,dbo.ProductMasterView.ExcludeXmasNewYear
    		,dbo.ProductMasterView.ExternalShelfLifeTotalDays
    		,dbo.ProductMasterView.Tagline
    		,dbo.ProductMasterView.CustShortName
    		,dbo.ProductMasterView.ExpiryRuleLabelText
    		,dbo.ProductMasterView.inner_hDescription
    		,dbo.ProductMasterView.inner_vDescription
    		,dbo.ProductMasterView.inner_CustCode
    		,dbo.ProductMasterView.inner_Storage
    		,dbo.ProductMasterView.inner_WarningMsg
    		,dbo.ProductMasterView.inner_Dateformat
    		,dbo.ProductMasterView.inner_SpecialCode
    		,dbo.ProductMasterView.LabelType
    		,dbo.ProductMasterView.inner_MicroSuiteCode
    		,dbo.ProductMasterView.inner_PackWeight
    		,dbo.ProductMasterView.inner_JDateFormat
    		,dbo.ProductMasterView.CustomOptionID
    		,dbo.Customers.PrintLogo AS inner_PrintLogo
    		,dbo.ProductMasterView.NominalWeight
    		,dbo.ProductMasterView.IngDec_html
    		,dbo.ProductMasterView.IngDec_Active
    	FROM dbo.Customers RIGHT OUTER JOIN dbo.ProductMasterView 
    		ON dbo.Customers.EndUser = dbo.ProductMasterView.EndUserCode 
    		RIGHT OUTER JOIN
    		(
    			SELECT ProductionOrderNumber AS JobNumber
    				,ManufacturingDepartment AS MfgDept
    				,U_DateRequired AS DateRequired
    				,x.ExpiryDate AS JobExpiryDate
    				,x.JulianDate AS JobJulianDate
    				,CAST(CASE WHEN LEFT(PartFileItemID, 1) = '9' THEN OrderQuantity ELSE 1 END AS int) AS LabelCopies
    				,CAST(PartFileItemID AS varchar(12)) AS PartFileItemID
    				,Batch
    				,x.WORDERKey AS WORDERKey
    			FROM F8Extract.dbo.WORDER OUTER APPLY [dbo].[getJobDetails](ProductionOrderNumber,PartFileItemID) AS x
    			WHERE	(LEFT(PartFileItemID, 1) IN ('8', '9'))
    			UNION
    			SELECT	'PROOF_' + PartFileItemID AS Expr1
    				,NULL AS Expr2
    				,NULL AS Expr3
    				,NULL AS JobExpiryDate
    				--,(SELECT TOP (1) ExpiryDate FROM dbo.PrintJobs AS jed WHERE (JobNumber = 'PROOF_' + dbo.ProductMaster.PartFileItemID) ORDER BY PrintJobsID DESC) AS JobExpiryDate
    				,'JDATE' AS Expr4
    				,1 AS Expr5
    				,PartFileItemID
    				,'PROOF' AS Expr6
    				,NULL AS Expr7
    			FROM dbo.ProductMaster WHERE (LabelStatus = 'R')
    		) AS wo 
    		ON dbo.ProductMasterView.PartFileItemID = wo.PartFileItemID
    END

    Thanks,

    Stacy

    Monday, July 07, 2014 10:40 AM

Answers

  • Hi Stacy,

    I am not sure about what the overall logic of the stored procedure is meant to achieve. However in terms of improving performance. I'd make sure that columns references with your JOIN and WHERE clause are indexed, i.e. dbo.Customers.EndUser, dbo.ProductMasterView.EndUserCode, PartFileItemID, LabelStatus, dbo.ProductMasterView.PartFileItemID and wo.PartFileItemID.

    The following is generally the best forum for raising this type of question; - http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/



    Monday, July 07, 2014 9:50 PM