I have 2 stored procedures to filter data and populate another table. The first procedure is providing a parameter for the second, I have many similar conditions but I only show one
CREATE PROCEDURE [dbo].[GetCodTestSSM] @CodCompartiment NVARCHAR(10), @CodTest NVARCHAR(2) OUTPUT AS BEGIN IF @CodCompartiment IN (SELECT value FROM STRING_SPLIT('A0,A1.0,A1.1,A1.1.0.3,A15,A20.3',',')) SET @CodTest = 'TB'; END
The second procedure
CREATE PROCEDURE [dbo].[AddTestDetailSSM] @IdUserTest AS int, @Complexitate AS nvarchar(50), @CodCompartiment AS nvarchar(50), @CodTest as nvarchar (2) AS BEGIN IF (@Complexitate = 'GENERAL') EXECUTE AddTestDetailSSM @CodCompartiment, @CodTest OUTPUT; SELECT @CodTest; BEGIN INSERT INTO tblTestDetailSSM (IdIntrebare, IdUserTest) SELECT TOP 10 ti.IdIntrebare AS IdIntrebare, @IdUserTest FROM (SELECT TOP 100 * FROM tblIntrebare ORDER BY NEWID()) ti JOIN tblProcedura tp ON ti.IdProcedura = tp.IdProcedura WHERE tp.Complexitate = 'GENERAL' AND tp.Specialitate = 'SSM' AND @CodTest IN (SELECT * FROM STRING_SPLIT(tp.CodTEST,',')); END IF (@Complexitate = 'SPECIFIC') EXECUTE AddTestDetailSSM @CodCompartiment, @CodTest OUTPUT; SELECT @CodTest; BEGIN INSERT INTO tblTestDetailSSM (IdIntrebare, IdUserTest) SELECT TOP 10 ti.IdIntrebare AS IdIntrebare, @IdUserTest FROM (SELECT TOP 100 * FROM tblIntrebare ORDER BY NEWID()) ti JOIN tblProcedura tp ON ti.IdProcedura = tp.IdProcedura WHERE tp.Complexitate = 'GENERAL' AND tp.Specialitate = 'SSM'AND @CodTest IN (SELECT * FROM STRING_SPLIT(tp.CodTEST,',')); INSERT INTO tblTestDetailSSM (IdIntrebare, IdUserTest) SELECT TOP 10 ti.IdIntrebare AS IdIntrebare, @IdUserTest FROM (SELECT TOP 100 * FROM tblIntrebare ORDER BY NEWID()) ti JOIN tblProcedura tp ON ti.IdProcedura = tp.IdProcedura WHERE tp.Complexitate = 'SPECIFIC' AND tp.Specialitate = 'SSM'AND @CodTest IN (SELECT * FROM STRING_SPLIT(tp.CodTEST,',')); END END
but I get nothing on execute.
I used the second procedure without getting data from first and is working so I suspect I am doing something wrong with the last WHERE condition but I can't find what.