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

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.

Code:

---

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

---

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

Code:

---

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

---

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

Code:

---

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

---