SQL server stored procedure fail to work

In the second procedure (AddTestDetailSSM), you're executing the GetCodTestSSM stored procedure to set the @CodTest parameter, but the way you're calling it might be incorrect. Specifically, you should call GetCodTestSSM properly with OUTPUT for the @CodTest parameter to ensure it is populated with the correct value.

CREATE PROCEDURE [dbo].[AddTestDetailSSM]

@IdUserTest AS int,
@Complexitate AS nvarchar(50),
@CodCompartiment AS nvarchar(50),
@CodTest as nvarchar(2) OUTPUT  -- Ensure @CodTest is passed as OUTPUT

AS
BEGIN
	 
	EXECUTE GetCodTestSSM @CodCompartiment, @CodTest OUTPUT;

	-- Select the @CodTest value for debugging purposes
	SELECT @CodTest;

	IF (@Complexitate = 'GENERAL')
	BEGIN
		-- Insert data for 'GENERAL' complexity
		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')
	BEGIN
		-- Insert data for 'GENERAL' complexity
		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 data for 'SPECIFIC' complexity
		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

I changed the EXECUTE AddTestDetailSSM to EXECUTE GetCodTestSSM @CodCompartiment, @CodTest OUTPUT; to correctly populate the @CodTest value.

You're executing the stored procedure AddTestDetailSSM and expecting it to correctly populate the @CodTest parameter by calling GetCodTestSSM, but the procedure might not be executing as expected, especially in terms of handling the output parameter.

Got it to work by modifying

EXECUTE SetCodTest @Functie, @CodCompartiment, @CodTest OUTPUT;

with

EXECUTE SetCodTest @Functie = @Functie, @CodCompartiment = @CodCompartiment, @CodTest = @CodTest OUTPUT

:( it is not but it worked for one time only

CREATE PROCEDURE [dbo].[AddTestDetailSSM]

@IdUserTest AS int,
@Complexitate AS nvarchar(50),
@CodCompartiment AS nvarchar(50),
@Functie AS nvarchar(100)

AS
BEGIN
	DECLARE @CodTest as nvarchar (2);
	EXECUTE GetCodTestSSM @Functie, @CodCompartiment, @CodTest OUTPUT;
	SELECT @CodTest;

	IF (@Complexitate = 'GENERAL')
		
	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.Specialitate = 'SSM' AND @CodTest IN (SELECT * FROM STRING_SPLIT(tp.CodTEST,','));
	END
	
	IF (@Complexitate = 'SPECIFIC')

	BEGIN
		INSERT INTO tblTestDetailSSM (IdIntrebare, IdUserTest) 
		SELECT TOP 20 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.Specialitate = 'SSM' AND @CodTest IN (SELECT * FROM STRING_SPLIT(tp.CodTEST,','));
	END
END

there is something not working properly here, parameters are sent corectly

its resolved?

I found the error. In the second procedure I was calling wrong procedure

EXECUTE AddTestDetailSSM @CodCompartiment, @CodTest OUTPUT;

should be

EXECUTE GetCodTestSSM @CodCompartiment, @CodTest OUTPUT;