SQL Server : table variable continues to give null values

Question

I am having an issue that keeps giving me grief. I am working on this E-Commerce project and the way that it's set up is to use a table variable to create it to do some comparison functions for a wish list feature I am working on. Pretty much if the best price coming from the MasterProduct table is less then the currently stored price, get that value and store that ID.

The issue is that I am always getting a null reference error when I try to use this. I am very new to creating table variables in SQL so I am completely lost as to what to do. Could anyone be able to help me understand how I could fix this issue?

CREATE PROCEDURE [dbo].[DAL_Wishlist_SelectAllPersonIDByPriceDrop] 
    (@iErrorCode INT OUTPUT) 
AS 
    SET NOCOUNT ON

    DECLARE @MasterProductIDTable TABLE 
            ( 
                MasterProductID [int] NOT NULL,
                PRIMARY KEY (MasterProductID)
            ) 

    INSERT INTO @MasterProductIDTable (MasterProductID)
        SELECT DISTINCT [MasterProduct].[MasterProductID]
        FROM [dbo].[Wishlist], [dbo].[MasterProduct]
        WHERE [MasterProduct].[MasterProductID] = [Wishlist].[MasterProductID] 
          AND [MasterProduct].[BestPrice] < [Wishlist].[PreviousCost]

    SELECT DISTINCT [Wishlist].[PersonID] 
    FROM [dbo].[Wishlist], @MasterProductIDTable mpt
    WHERE [Wishlist].[MasterProductID] = mpt.MasterProductID

    SELECT @iErrorCode = @@ERROR
GO

The stored procedure is rather simple in that it's:

private List<Wishlist> _loadAllFromDataTable(DataTable dt)
{
    List<Wishlist> list = new List<Wishlist>();

    for (int index = 0; index < dt.Rows.Count; index++)
    {
        list.Add(_loadFromDTRow(index, dt));
    }

    return list;
}

private Wishlist _loadFromDTRow(int row, DataTable dt)
{
    if (dt.Rows.Count > 0)
    {
        Wishlist wishlist = new Wishlist();
        wishlist.ID = (int)dt.Rows[row]["ID"];
        wishlist.PersonID = (int)dt.Rows[row]["PersonID"];
        wishlist.MasterProductID = (int)dt.Rows[row]["MasterProductID"];
        wishlist.DateAdded = (DateTime)dt.Rows[row]["DateAdded"];
        wishlist.PreviousCost = (decimal)dt.Rows[row]["PreviousCost"];

        return wishlist;
    }
    else 
        return null;
}

And:

public List<int> SelectAllPersonIDByPriceDrop()
{
    SqlCommand scmCmdToExecute = new SqlCommand();
    scmCmdToExecute.CommandText = "dbo.[DAL_Wishlist_SelectAllPersonIDByPriceDrop]";
    scmCmdToExecute.CommandType = CommandType.StoredProcedure;

    scmCmdToExecute.Parameters.Add(new SqlParameter("@iErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, true, 10, 0, "", DataRowVersion.Proposed, (SqlInt32)0));

    DataTable dt = SqlDB.ExecuteQuery(scmCmdToExecute);

    List<int> masterProductIDList = new List<int>();

    for (int index = 0; index < dt.Rows.Count; index++)
    {
        int masterProductID = (int)dt.Rows[index]["PersonID"];
        masterProductIDList.Add(masterProductID);
    }

    return masterProductIDList;
}

Any help would be GREATLY appreciated.

Thanks.

Answer 1

You didn't specify where the NULL (specifically) occurs so I suspect this should help:

var dt = new DataTable();
dt = SqlDB.ExecuteQuery(scmCmdToExecute);

Answer 2

I do not think you need to worry about an error code with a select query, which your procedure can easily be distilled down to:

create procedure dbo.dal_Wishlist_SelectAllPersonIDbyPriceDrop as
begin;
  set nocount on;
  select distinct w.PersonId
  from dbo.Wishlist w
    inner join dbo.MasterProduct mp
      on mp.MasterProductID = w.MasterProductID 
  where mp.BestPrice < w.PreviousCost
end;
go

or using exists():

create procedure dbo.dal_Wishlist_SelectAllPersonIDbyPriceDrop as
begin;
  set nocount on;
  select distinct w.PersonId
  from dbo.Wishlist w
  where exists (
    select 1
    from dbo.MasterProduct mp
    where mp.MasterProductID = w.MasterProductID 
      and mp.BestPrice < w.PreviousCost
    )
end;
go

Answer 3

Your procedure could be greatly simplified to a single select statement and no table variables. I have no idea what your intention is for @iErrorCode, it is not referenced at all in your code. Also, you need to select ALL the columns you want to use in the code. You are referencing columns in the c# not in your query. I am not sure which table those come from so you will need to add them.

CREATE PROCEDURE [dbo].[DAL_Wishlist_SelectAllPersonIDByPriceDrop] 
(
    @iErrorCode int OUTPUT
) AS 

    SET NOCOUNT ON;

    SELECT w.PersonID
    FROM Wishlist w
    JOIN dbo.MasterProduct mp ON mp.MasterProductID = w.MasterProductID 
            AND mp.BestPrice < w.PreviousCost
    GROUP BY w.PersonID;