[RESOLVED] Help With Stored Procedure (Variable showing as null)

[bmahler]

I have this stored procedure that I am passing variables into and I keep getting an error saying that the variable can not be null

the variable being passed that is showing as null is @TPK

Thanks for any help with this. Also let me add, that I am not a DBA so there may be many things wrong with this, it is what I was able to piece together from searches online. Also, I have to use sp_executesql because I need to dynamically get the schema name.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_AddParameterValue] 
	@ParameterType INT,
	@TPK INT, 
	@Param VarChar(100), 
	@Value VarChar(100), 
	@Comments Text	
	AS
	DECLARE @PPK INT,@i INT,@schema VarChar(50),@sql NVarChar(512)
	IF @ParameterType=1
		BEGIN
			SET @schema = (SELECT schema_name());
			SET @sql = N'SELECT ParameterPK FROM ['+@schema+'].[Parameters] WHERE Parameter = '''+@Param+''' AND ParameterType = 1';
			EXEC sp_executesql 
			@query = @sql, 
			@params = N'@PPK INT OUTPUT', 
			@PPK = @PPK OUTPUT;
			SET @PPK = @PPK;
			SET @sql = 'INSERT INTO [' + @schema + '].[AirToParameter] (AirPK,ParameterPK,Value,Comment) VALUES (@TablePK,@ID,@Val,@Comm)';
			EXEC sp_executesql
			@query = @sql,
			@params = N'@TablePK INT, @ID INT, @Val VarChar(50), @Comm VarChar(255)',
			@TablePK = @TPK, @ID = @i, @Val = @Value, @Comm = @Comments;
		END 
	IF @ParameterType=2
		BEGIN
			SET @schema = (SELECT schema_name());	
			SET @sql = N'SELECT ParameterPK FROM ['+@schema+'].[Parameters] WHERE Parameter = '''+@Param+''' AND ParameterType = 2';
			EXEC sp_executesql 
			@query = @sql, 
			@params = N'@PPK INT OUTPUT', 
			@PPK = @PPK OUTPUT;
			SET @i = @PPK;
			SET @sql = 'INSERT INTO [' + @schema + '].[AnimalToParameter] (AnimalPK,ParameterPK,Value,Comment) VALUES (@TablePK,@ID,@Val,@Comm)';
			EXEC sp_executesql
			@query = @sql,
			@params = N'@TablePK INT, @ID INT, @Val VarChar(50), @Comm VarChar(255)',
			@TablePK = @TPK, @ID = @i, @Val = @Value, @Comm = @Comments 		
		END	
	IF @ParameterType=3 
		BEGIN
			SET @schema = (SELECT schema_name());
			SET @sql = N'SELECT ParameterPK FROM ['+@schema+'].[Parameters] WHERE Parameter = '''+@Param+''' AND ParameterType = 3';
			EXEC sp_executesql 
			@query = @sql, 
			@params = N'@PPK INT OUTPUT', 
			@PPK = @PPK OUTPUT;
			SET @i = @PPK;
			SET @sql = 'INSERT INTO [' + @schema + '].[PlantToParameter] (PlantPK,ParameterPK,Value,Comment) VALUES (@TablePK,@ID,@Val,@Comm';
			EXEC sp_executesql
			@query = @sql,
			@params = N'@TablePK INT, @ID INT, @Val VarChar(50), @Comm VarChar(255)',
			@TablePK = @TPK, @ID = @i, @Val = @Value, @Comm = @Comments; 
		END
	IF @ParameterType=4 
		BEGIN
			SET @schema = (SELECT schema_name());
			SET @sql = N'SELECT ParameterPK FROM ['+@schema+'].[Parameters] WHERE Parameter = '''+@Param+''' AND ParameterType = 4';
			EXEC sp_executesql 
			@query = @sql, 
			@params = N'@PPK INT OUTPUT', 
			@PPK = @PPK OUTPUT;
			SET @i = @PPK;
			SET @sql = 'INSERT INTO [' + @schema + '].[SoilToParameter] (SoilPK,ParameterPK,Value,Comment) VALUES (@TablePK,@ID,@Val,@Comm)';
			EXEC sp_executesql
			@query = @sql,
			@params = N'@TablePK INT, @ID INT, @Val VarChar(50), @Comm VarChar(255)',
			@TablePK = @TPK, @ID = @i, @Val = @Value, @Comm = @Comments; 
		END
	IF @ParameterType=5 
		BEGIN
			SET @schema = (SELECT schema_name());
			SET @sql = N'SELECT ParameterPK FROM ['+@schema+'].[Parameters] WHERE Parameter = '''+@Param+''' AND ParameterType = 5';
			EXEC sp_executesql 
			@query = @sql, 
			@params = N'@PPK INT OUTPUT', 
			@PPK = @PPK OUTPUT;
			SET @i = @PPK;	
			SET @sql = 'INSERT INTO [' + @schema + '].[WaterToParameter] (WaterPK,ParameterPK,Value,Comment) VALUES (@TablePK,@ID,@Val,@Comm)';
			EXEC sp_executesql
			@query = @sql,
			@params = N'@TablePK INT, @ID INT, @Val VarChar(50), @Comm VarChar(255)',
			@TablePK = @TPK, @ID = @i, @Val = @Value, @Comm = @Comments; 
		END
	IF @ParameterType=6 
		BEGIN
			SET @schema = (SELECT schema_name());
			SET @sql = N'SELECT ParameterPK FROM ['+@schema+'].[Parameters] WHERE Parameter = '''+@Param+''' AND ParameterType = 6';
			EXEC sp_executesql 
			@query = @sql, 
			@params = N'@PPK INT OUTPUT', 
			@PPK = @PPK OUTPUT;
			SET @i = @PPK;
			SET @sql = 'INSERT INTO [' + @schema + '].[AnalyteToParameter] (AnalytePK,ParameterPK,Value,Comment) VALUES (@TablePK,@ID,@Val,@Comm)';
			EXEC sp_executesql
			@query = @sql,
			@params = N'@TablePK INT, @ID INT, @Val VarChar(50), @Comm VarChar(255)',
			@TablePK = @TPK, @ID = @i, @Val = @Value, @Comm = @Comments;
		END