Cannot Send Parameter to Stored Procedure [Answered]RSS

• • Reply
  munirah-mali...

  Member

  3 Points

  13 Posts

  Cannot Send Parameter to Stored Procedure

  Nov 19, 2018 09:09 AM|munirah-malik1|LINK

  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

• • Reply
  PatriceSc

  All-Star

  42430 Points

  14547 Posts

  Re: Cannot Send Parameter to Stored Procedure

  Nov 19, 2018 02:00 PM|PatriceSc|LINK

  Hi,

  Dynamic SQL runs inside another "context" where those parameters are not know so you still have to explicitely pass those values :

  EXEC sp_executesql @DynamicPivotQuery,N'@KT_KEIRO_NAME_VAR NVARCHAR(max),@KT_GUN_NO_VAR NVARCHAR(max)',@KT_KEIRO_NAME_VAR,@KT_GUN_NO_VAR

  Just see the doc for sp_executesql at https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017