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