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
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.
Here is the stored procedure.
How I want to send the parameter to SP. Please help
All-Star
48710 Points
18175 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