locked
Cannot Send Parameter to Stored Procedure RRS feed

  • Question

  • User929636964 posted

    Hi All,

    I'm having one stored procedure.  Error display when I try to pass parameter from system.

    Must declare the scalar variable "@KT_KEIRO_NAME_VAR".

    Here is the stored procedure.

    ALTER PROCEDURE [dbo].[EXEC_DATETM_SP2X]
    @KT_KEIRO_NAME_VAR Nvarchar(MAX), @KT_GUN_NO_VAR Nvarchar(MAX)
    
    AS
    BEGIN
    	SET NOCOUNT ON;
    	DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    	DECLARE @ColumnName NVARCHAR(MAX)
    
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
           + QUOTENAME(EXEC_DATETMM)
    FROM (SELECT distinct([EXEC_DATETMM]) FROM [WAFERRPTDB].[dbo].[V_COMBINE_WLQTQFLQ]) AS EXEC_DATETMM
    
    
        set @DynamicPivotQuery='
    	Select [KT_HYOU_NAME],
    	[KT_KEIRO_NAME],
    	[KT_GUN_NO],
    	[TUKA_JUN],
    	[KT_CD],
    	[SG_KU_CD],
    	' + @ColumnName + '
    		from 
    	(
    		SELECT * FROM V_DATEBYWLQ
    		where KT_KEIRO_NAME=@KT_KEIRO_NAME_VAR
            AND KT_GUN_NO=@KT_GUN_NO_VAR
    	) s
        pivot (MAX (WORK_LOT_QTY) for 
        [EXEC_DATETMM] in ('+ @ColumnName +')) as AvgWidgetsPerDayPerEmp
    	'
    EXEC sp_executesql @DynamicPivotQuery
    END

    How I want to send the parameter to SP. Please help

    Monday, November 19, 2018 9:09 AM

Answers