I am passing a json string from my controller to the stored procedure. When I am trying to read the data from my json and storing in a temp table, its not working. The temp table show null for all the columns. I am not sure if the jsonstring is inn wrong format or what.
declare
@FirstLevelFilter nvarchar(100) = '',
@SecondLevelFilter nvarchar(100) = '',
@WorkspaceId int = 0,
@EFITTypeId int = 0,
@EFITVersion decimal (5,2),
@StartYear int = 0,
@EndYear int = 0,
@Years nvarchar(max) = '',
@EStaffGroups nvarchar(max) = '' ,
@EFITTypeName int = 0,
@JSONGlobalFilter nvarchar(max) = ''
set @FirstLevelFilter = 'HCCBCC'
set @SecondLevelFilter = 'CostCenter'
set @StartYear = 0
set @EndYear = 0
set @Years = ''
set @EStaffGroups = ''
set @EFITTypeName = 0
set @JSONGlobalFilter = '{
"workspaceId":["4","441"],
"efitId":[],
"modelId":[],
"buildingBlockId":[],
"titleBlockId":[],
"workPackageId":[],
"estimateStatusId":[],
"estimateTypeId":[],
"recordStatusId":[]
}'
drop table if exists #tempGlobalFilters
select
workspaceId,
efitId,
modelId,
buildingBlockId,
titleBlockId,
workPackageId,
estimateStatusId,
estimateTypeId,
recordStatusId,
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS SNO
into #tempGlobalFilters
from openjson (@JSONGlobalFilter)
with
(
workspaceId nvarchar(max),
efitId nvarchar(max),
modelId nvarchar(max),
buildingBlockId nvarchar(max),
titleBlockId nvarchar(max),
workPackageId nvarchar(max),
estimateStatusId nvarchar(max),
estimateTypeId nvarchar(max),
recordStatusId nvarchar(max)
) as GlobalFilters
select * from #tempGlobalFilters