Dynamic Stored Procedure with Output Parameter

Hare,  I give you an Example of Dynamic Stored Procedure with output parameter.

/****** Object:  StoredProcedure [dbo].[USP_Rpt_GetTotalAmountFromGLAmountByGLCategoryForCFY]    Script Date: 09/14/2012 11:05:11 ******/

 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Rpt_GetTotalAmountFromGLAmountByGLCategoryForCFY]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[USP_Rpt_GetTotalAmountFromGLAmountByGLCategoryForCFY]

G0

 

/****** Object:  StoredProcedure [dbo].[USP_Rpt_GetTotalAmountFromGLAmountByGLCategoryForCFY]    Script Date: 09/14/2012 11:05:11 ******/

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

-- =============================================

-- Author:JAYENDRASINH GOHIL

-- Create date:13/09/2012

-- Description:   <Description,,>

-- =============================================

 

CREATE PROCEDURE [dbo].[USP_Rpt_GetTotalAmountFromGLAmountByGLCategoryForCFY]

(

      @Days INT = NULL,

      @G_LCategoryCode VARCHAR(MAX),

      @FiscalYear_StartingDate DATETIME , 

      @FiscalYear_EndingDate DATETIME,

      @TotalAmount DECIMAL(38,2) OUTPUT

)

AS

BEGIN

      DECLARE @TotalAmmount DECIMAL(38, 20)

      DECLARE @Query           VARCHAR(MAX) = ''

      DECLARE @nQuery           NVARCHAR(MAX)

      DECLARE @ParamDefinition  NVARCHAR(MAX)

     

      SET @ParamDefinition = '@Days INT = NULL,

      @G_LCategoryCode VARCHAR(MAX),

      @TotalAmount DECIMAL(38,2) OUTPUT'

      SET @Query = 'SELECT @TotalAmount =  ISNULL(SUM(GLEA.[Amount]),0)   FROM [dbo].[Rpt_View_G_L_Entry_Account] GLEA WHERE GLEA.[G_L Category Code] LIKE ''' + '%' + @G_LCategoryCode + '%'''

      + ' AND (GLEA.[posting date] BETWEEN ''' + convert (nvarchar(14), @FiscalYear_StartingDate, 103)   +  ''' AND  ''' +  convert (nvarchar(14), @FiscalYear_EndingDate, 103)  + ''' ) '

      IF(@Days != 365)

       BEGIN

        SET   @Query += ' AND                         

        GLEA.[posting date] >= GetDate() - ' + CONVERT(VARCHAR, @Days )+ ' AND GLEA.[posting date] <= GetDate() '

       END                                                           

      SELECT @nQuery = CAST(@Query AS NVARCHAR(MAX))

      PRINT @nQuery     

      EXECUTE sp_Executesql   @nQuery,@ParamDefinition,@Days,@G_LCategoryCode,@TotalAmount = @TotalAmount OUTPUT

END

GO

Now test the stored procedure

DECLARE @return_value int,

@TotalAmount decimal(38, 2)

EXEC  @return_value = [dbo].[USP_Rpt_GetTotalAmountFromGLAmountByGLCategoryForCFY]

            @Days = 120,

            @G_LCategoryCode = N'EXPENSES',

            @FiscalYear_StartingDate = N'2012-04-01 00:00:00.000',

            @FiscalYear_EndingDate = N'2013-03-01 00:00:00.000',

            @TotalAmount = @TotalAmount OUTPUT

SELECT      @TotalAmount as N'@TotalAmount'

SELECT 'Return Value' = @return_value

GO