Marius Vasile

Marius Vasile

  • 592
  • 1.9k
  • 145.5k

SQL server stored procedure fail to work

Aug 3 2024 6:11 AM

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.


Answers (4)