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.

A quick note on vocabulary- the stored procedure above uses a "Table Variable" and not a "Temp Table." There are similarities between these concepts, but they are not the same thing. — Ryan B.
– Ryan B., Commented Jun 2, 2017 at 15:07
it seems that your sql is returning the @@error variable as output. try SET @iErrorCode=@@ERROR — Jayvee
– Jayvee, Commented Jun 2, 2017 at 15:07
You really should learn to use aliases for your tables and start using ANSI-92 style joins, they have been around for over 25 years now. — Sean Lange
– Sean Lange, Commented Jun 2, 2017 at 15:09
I would change your joins to ANSI INNER JOINS rather than old style joins - see sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins — Steve Ford
– Steve Ford, Commented Jun 2, 2017 at 15:11

Sean Lange · Accepted Answer · 2017-06-02 15:14:16Z

2

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;

answered Jun 2, 2017 at 15:14

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SqlZim Over a year ago

I was thinking the same thing. +1

Sean Lange Over a year ago

I used group by instead of distinct but it is hard to know what the OP is really trying to do here.

Paul Laudun Over a year ago

Thanks. When I get back to work on Monday I'll try this out to see if this works.

SqlZim · Accepted Answer · 2017-06-02 15:14:33Z

1

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

answered Jun 2, 2017 at 15:14

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

1 Comment

Paul Laudun Over a year ago

I like this answer as well. I'll give it a try when I get back to work. Thanks.

Bill Roberts · Accepted Answer · 2017-06-02 15:38:20Z

0

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

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

edited Jun 2, 2017 at 15:38

answered Jun 2, 2017 at 15:12

Bill Roberts

1,17119 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL Server : table variable continues to give null values

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related