[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

[bmahler]

I ended up getting my answer. If anyone is interested, here is the corrected version.

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 @PPK = ParameterPK FROM ['+@schema+'].[Parameters] WHERE Parameter = '''+@Param+''' AND ParameterType = 1';
			EXEC sp_executesql 
			@query = @sql, 
			@params = N'@PPK INT OUTPUT', 
			@PPK = @PPK OUTPUT
			SET @i = (SELECT @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 @PPK = 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 = (SELECT @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 @PPK = 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 = (SELECT @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 @PPK = 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 = (SELECT @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 @PPK = 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 = (SELECT @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 @PPK = 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 = (SELECT @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

[brucevde]

That code would be tough to debug!

The only difference I found for each ParameterType block is the Select statement and the Insert statement. It could be simplified to

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)
   
SET @schema = (SELECT schema_name())

SET @sql = N'SELECT @PPK = ParameterPK FROM ['+@schema+'].[Parameters] WHERE Parameter = '''+@Param+''' AND ParameterType = ' + Convert(varchar(1), @ParameterType)

EXEC sp_executesql  @query = @sql,  @params = N'@PPK INT OUTPUT', @PPK = @PPK OUTPUT

SET @i = (SELECT @PPK)

SET @SQL = 'INSERT INTO [' + @schema + '].[TOKEN_ToParameter] (TOKEN_PK,ParameterPK,Value,Comment) VALUES (@TablePK,@ID,@Val,@Comm)' 

--replace TOKEN_ with the correct word based on the ParameterType.
If @ParameterType = 1  Set @Insert = Replace(@Insert,'TOKEN_', 'Air')
If @ParameterType = 2  Set @Insert = Replace(@Insert,'TOKEN_', 'Animal') 
If @ParameterType = 3  Set @Insert = Replace(@Insert,'TOKEN_', 'Plant') 
If @ParameterType = 4  Set @Insert = Replace(@Insert,'TOKEN_', 'Soil') 
If @ParameterType = 5  Set @Insert = Replace(@Insert,'TOKEN_', 'Water') 
If @ParameterType = 6  Set @Insert = Replace(@Insert,'TOKEN_', 'Analyte')
  
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