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