CODEWITHSUNDEEP
sql-servert-sqlreporting-services
Don Ford
Don Ford

Reputation: 115

Trying to write dynamic parameter stored procedure

I'm trying to write a dynamic parameter stored procedure to feed into an SSRS report. I wrote this after browsing a LOT of websites, looking for ways to allow my end-users to custom-select which parameters they want to use. An added kink in the process is that if no End parameter is used on a range, then it is assumed that the end-user is only interested in the value entered in the Start field.

Here's what I have so far. Why am I locking up SSRS when I try to run the report?

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[BrowseCODA] 
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DocList TABLE ([DocumentID] varchar(32) ) 

    DECLARE @GLAccountStart int = NULL
    DECLARE @GLAccountEnd int = NULL
    DECLARE @CustomerID int = NULL
    DECLARE @VendorID int = NULL
    DECLARE @StoreID nvarchar(2) = NULL
    DECLARE @DocDateStart date = NULL
    DECLARE @DocDateEnd date = NULL
    DECLARE @InputDateStart date = NULL
    DECLARE @InputDateEnd date = NULL
    DECLARE @AcctPeriodStart int = NULL
    DECLARE @AcctPeriodEnd int = NULL
    DECLARE @AcctYearStart int = NULL
    DECLARE @AcctYearEnd int = NULL
    DECLARE @PaymentID int = NULL
    DECLARE @ExtRef1 nvarchar(30) = NULL
    DECLARE @ExtRef2 nvarchar(30) = NULL
    DECLARE @ExtRef3 nvarchar(30) = NULL
    DECLARE @ExtRef4 nvarchar(30) = NULL
    DECLARE @ExtRef5 nvarchar(30) = NULL
    DECLARE @ExtRef6 nvarchar(30) = NULL

    INSERT INTO @DocList( [DocumentID] )
       SELECT
           company + '-' + FullDocNumber
       FROM 
           [DataWarehouse].[dbo].[VDocDetails]
       WHERE 
           ([GLAccount] >= @GLAccountStart OR @GLAccountStart IS NULL) 
           AND ((@GLAccountEnd IS NULL AND @GLAccountStart IS NOT NULL 
                 AND [GLAccount]=@GLAccountStart) 
                 OR (@GLAccountEnd IS NOT NULL AND [GLAccount] <= @GLAccountEnd) 
                 OR (@GLAccountEnd IS NULL))
           AND ([CustomerNumber] = @CustomerID OR @CustomerID IS NULL)
           AND ([Vendor] = @VendorID OR @VendorID IS NULL)
           AND ([Store] LIKE @StoreID+'%' OR @StoreID IS NULL)
           AND ([Doc_Date] >= @DocDateStart OR @DocDateStart IS NULL)
           AND ((@DocDateEnd IS NULL AND @DocDateStart IS NOT NULL 
                 AND [Doc_Date] = @DocDateStart) 
                OR (@DocDateEnd IS NOT NULL AND [Doc_Date] <= @DocDateEnd) 
                OR (@DocDateEnd IS NULL))
           AND ([Input_Date] >= @InputDateStart OR @InputDateStart IS NULL)
           AND ((@InputDateEnd IS NULL AND @InputDateStart IS NOT NULL
                 AND [Input_Date] = @InputDateStart) 
                OR (@InputDateEnd IS NOT NULL AND [Input_Date] <= @InputDateEnd)
                OR (@InputDateEnd IS NULL))
          AND ([period] >= @AcctPeriodStart OR @AcctPeriodStart IS NULL)
          AND ((@AcctPeriodEnd IS NULL AND @AcctPeriodStart IS NOT NULL 
                AND [period] = @AcctPeriodStart) 
               OR (@AcctPeriodEnd IS NOT NULL AND [period] <= @AcctPeriodEnd) 
               OR (@AcctPeriodEnd IS NULL))
          AND ([Year] >= @AcctYearStart OR @AcctYearStart IS NULL)
          AND ((@AcctYearEnd IS NULL AND @AcctYearStart IS NOT NULL 
                AND [Year] = @AcctYearStart) 
               OR (@AcctYearEnd IS NOT NULL AND [Year] <= @AcctYearEnd) 
               OR (@AcctYearEnd IS NULL))
          AND ([PaymentID] = @PaymentID OR @PaymentID IS NULL)
          AND ([Reference1] LIKE @ExtRef1+'%' OR @ExtRef1 IS NULL)
          AND ([Reference2] LIKE @ExtRef2+'%' OR @ExtRef2 IS NULL)
          AND ([Reference3] LIKE @ExtRef3+'%' OR @ExtRef3 IS NULL)
          AND ([Reference4] LIKE @ExtRef4+'%' OR @ExtRef4 IS NULL)
          AND ([Reference5] LIKE @ExtRef5+'%' OR @ExtRef5 IS NULL)
          AND ([Reference6] LIKE @ExtRef6+'%' OR @ExtRef6 IS NULL)

    SELECT
        *
    FROM 
        [DataWarehouse].[dbo].[VDocDetails]
    WHERE  
        company + '-' + FullDocNumber IN (SELECT DocumentID FROM @DocList)
END
GO

Upvotes: 0

Views: 44

Answers (1)

Slava Murygin
Slava Murygin

Reputation: 1955

Answer to your question:
Because you do not provide ANY variable to your stored procedure all internal variables are NULLs. That means ALL records from your table has to be extracted. That causes double scan of probably very huge table + memory issue with temp variable. At first, you probably code something like this:

CREATE PROCEDURE [dbo].[BrowseCODA] 
@GLAccountStart int = NULL
, @GLAccountEnd int = NULL
, @CustomerID int = NULL
, @VendorID int = NULL
, @StoreID nvarchar(2) = NULL
, @DocDateStart date = NULL
, @DocDateEnd date = NULL
, @InputDateStart date = NULL
, @InputDateEnd date = NULL
, @AcctPeriodStart int = NULL
, @AcctPeriodEnd int = NULL
, @AcctYearStart int = NULL
, @AcctYearEnd int = NULL
, @PaymentID int = NULL
, @ExtRef1 nvarchar(30) = NULL
, @ExtRef2 nvarchar(30) = NULL
, @ExtRef3 nvarchar(30) = NULL
, @ExtRef4 nvarchar(30) = NULL
, @ExtRef5 nvarchar(30) = NULL
, @ExtRef6 nvarchar(30) = NULL

AS
BEGIN
SET NOCOUNT ON;

At second, do not use temp table variable, just report directly from table. At third, better use dynamic SQL to build query for Non-NULL values only with OPTION RECOMPILE.

Upvotes: 1

Related Questions