Invalid object name 'dbo.CategoryIdArray'

Question

My Motive is to pass long array of ID as parameter to stored procedure and select data on the basis of ID. So i created Type in SQL Server

CREATE TYPE [dbo].[CategoryIdArray] AS TABLE(
[CategoryId] [bigint] NULL
)

GO

and stored procedure

ALTER  PROCEDURE [dbo].[GetNewestArticleByCatsPageWise]
  @dt as [dbo].[CategoryIdArray] READONLY,
  @PageIndex INT = 1
  ,@PageSize INT = 10
  ,@PageCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
            (
                  ORDER BY [dateadded] 
            )AS RowNumber,[desid]


INTO #Results
  FROM [DB_user1212].[dbo].[discussions] as d , [DB_user1212].[dbo].[CategoryMap] as c where d.desid=c.[Topic Id] and c.[Category Id] in (select CategoryId from [dbo].[CategoryIdArray]) and [TopicType]='1' order by [dateadded]

  DECLARE @RecordCount INT
  SELECT @RecordCount = COUNT(*) FROM #Results

  SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
  PRINT       @PageCount

  SELECT * FROM #Results
  WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

  DROP TABLE #Results
END

Tried to use above stored procedure by Code below

 public List<String> getNewestArticleByCategoryPageWise( long[] categoryId)
  {
        List<string> topicId= new List<string>();
      try
      {

        DataTable dt_Categories = new DataTable();
        dt_Categories.Columns.Add("Category", typeof(String));
        DataRow workRow;
        foreach(long cat in categoryId)
        {
          workRow = dt_Categories.NewRow();
          workRow["Category"] = cat;
          dt_Categories.Rows.Add(workRow);
        }
        int pageIndex = 1;
            SqlCommand cmd = new SqlCommand("dbo.GetNewestArticleByCatsPageWise", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", 10);
            cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
            SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", dt_Categories);
            tvparam.SqlDbType = SqlDbType.Structured;
          con.Open();
            sdr= cmd.ExecuteReader();
      while(sdr.Read())
      {
          topicId.Add(sdr.GetString(0));
      }
          con.Close();
      }
      catch(Exception ex)
      {
          con.Close();
          throw ex;
      }
      return topicId;

  }

When i run above function exception is thrown Invalid object name 'dbo.CategoryIdArray'. But i created it as type. Help me out what i missed out. I refferred this.

Answer 1

Problem is with this line in stored procedure is with this line

select CategoryId from [dbo].[CategoryIdArray] .

We can not select from type like this, we should use

select CategoryId from @dt

Answer 2

The first thing that I do when I get these questions is to create a sample database. The code below creates the following.

1 - database named [test]

2 - table named [Discussions]

3 - table named [CategoryMap]

4 - user defined table type named [CategoryIdArray]

5 - load the tables with 100 records of data

--
-- Create a test db
--

USE [master];
go

CREATE DATABASE [Test];
GO

--
-- Create the user defined type
--

USE [Test];
go

CREATE TYPE [CategoryIdArray] AS 
TABLE
(
[CategoryId] [bigint] NULL
);



--
-- Create skelton tables
--

create table Discussions
(
  dis_id int identity (1,1),
  dis_name varchar(64),
  dis_added_dte datetime default getdate()
);
go

create table CategoryMap
(
  cat_id int identity(1,1),
  cat_topic_id int,
  cat_topic_type char(1)
);
go

-- clear tables
truncate table Discussions;
truncate table CategoryMap;
go


--
-- Create 100 rows of dummy data
--

declare @cnt int = 0;
while @cnt < 100
begin
    insert into Discussions (dis_name) 
    values ('sample discussion record # ' + str(@cnt, 2, 0));

    insert into CategoryMap (cat_topic_id, cat_topic_type) 
    values (@cnt+1, '1')

    set @cnt = @cnt + 1;
end;
go


--
-- Show the sample data
--

select * from Discussions;
go

select * from CategoryMap;
go

The second step is to re-write the stored procedure. If you are using below 2012, go with a window function rownumber(). In 2012, the offset and fetch clauses of the order by were included for paging.

http://technet.microsoft.com/en-us/library/ms188385(v=sql.110).aspx

--
-- Create my procedure
--

create  procedure [GetArticlesByPage]
  @Tvp as [CategoryIdArray] READONLY,
  @PageIndex INT = 1,
  @PageSize INT = 10,
  @PageCount INT OUTPUT
AS
BEGIN

  -- Declare variables
  DECLARE @var_recs int = 0;
  DECLARE @var_offset int = 0;

  -- Do not count the records
  SET NOCOUNT ON;

  -- Start of paging
  SET @var_offset = @var_offset + ((@PageIndex - 1) * @PageSize);

  -- Set page count variable
  SELECT @var_recs = count(*)
  FROM 
      [dbo].[Discussions] as d 
  JOIN 
      [dbo].[CategoryMap] as c 
  ON 
      d.dis_id = c.cat_topic_id 
  JOIN 
      @TVP a 
  ON 
      c.cat_id = a.CategoryId
  WHERE 
      cat_topic_type  =  '1';

  set @PageCount = ceiling(cast(@var_recs as real) / cast(@PageSize as real));


  --
  -- Return the record set
  -- 

  SELECT 
      dis_id
  FROM 
      [dbo].[Discussions] as d 
  JOIN 
      [dbo].[CategoryMap] as c 
  ON 
      d.dis_id = c.cat_topic_id 
  JOIN 
      @TVP a 
  ON 
      c.cat_id = a.CategoryId
  WHERE 
      cat_topic_type  =  '1'
  ORDER BY 
      dis_added_dte
  OFFSET @var_offset ROWS 
  FETCH NEXT @PageSize ROWS ONLY;

END;
GO

I did leave the page count in place; However, I do not think it is needed since you can repeat the call until the result set is empty.

Please do not dump the record set into a temporary table since it could be quite large if you were return all the columns to display. I choose two separate calls. One for a total count. One for a single page.

The last TSQL part is to test the stored procedure from SSMS.

--
-- Call the stored procedure
--

-- instantiate tvp
DECLARE @my_tvp as [CategoryIdArray];
DECLARE @my_page_cnt as int;

-- add 25 entries
declare @cnt int = 25;
while @cnt < 50
begin
    insert into @my_tvp (CategoryId) 
    values (@cnt + 1);
    set @cnt = @cnt + 1;
end;

-- show the data in the tvp
select * from  @my_tvp

-- call the function
exec [GetArticlesByPage] @my_tvp, 1, 10, @PageCount = @my_page_cnt OUTPUT;

-- show the data in the output
select @my_page_cnt as 'my_pages';

go    

In my test example, I wanted rows 26 to 50 paged as 10 rows. Result 1 is the 25 rows, Result 2 is the 10 rows that were paged, and Result 3 is how many pages. Therefore, the TSQL part of the solution is sound.

Stay tuned for a C# program debug session later tonight.

http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

Take a look at this post. It is doing exactly what you are trying to do.

Here are some ideas to try.

1 - Make sure the connection properties, login's default database is [Test] for my example.

2 - Is the type defined in the [Test] database? Please double check this.

3 - Is this correct? The column name is [CategoryId] in the database type. You have the following - [Category]. Try changing the name in the C# code.

dt_Categories.Columns.Add("Category", typeof(String));

4 - Remove the [dbo]. from the type in the SP. It is not in the example from MS SQL Tips. Might be confusing the issue. SQL server will resolve the name.

5 - I noticed the type is defined as big int but the id in the tables is int? Make sure the data types are consistent.

Please try these suggestions. Get back to me on how you make out.

Can you get me a detailed call stack trace and error message if this is still an issue??

So here is a C# console application that I promised.

It works as expected.

You were mixing up some ideas that are the foundation of ADO.NET and data tables. You should get used to looking at the immediate window and local variables. This will help you track down issues.

Here is my sample call to the Stored Procedure.

1 - Setup data table (50 to 74)

2 - Page the data by 5's

3 - Look at second page

//
//  Good Ref. - http://msdn.microsoft.com/en-us/library/ms254937(v=vs.110).aspx
//

// Basic stuff from C# console app
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

// Required for data table
using System.Data;
using System.Data.SqlClient;

// Standard stuff ...
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            // Debug info
            Console.WriteLine("Test - Start");

            // Create the table with one column
            DataTable my_Table;
            my_Table = new DataTable("Category");
            my_Table.Columns.Add("CategoryId", typeof(string));

            // Add data to table
            for (int my_Cnt = 50; my_Cnt < 75; my_Cnt++)
            {
                DataRow my_Row = my_Table.NewRow();
                my_Row["CategoryId"] = my_Cnt.ToString();
                my_Table.Rows.Add(my_Row);
            }

            // Debug info
            Console.WriteLine("Test - created data set");

            // Create a connection
            SqlConnection my_Conn;
            string str_Conn = "Server=localhost;Database=Test;Trusted_Connection=True;";
            my_Conn = new SqlConnection(str_Conn);

            // Debug info
            Console.WriteLine("Test - create connection");

            // Create the command and set its properties.
            SqlCommand my_Cmd = new SqlCommand();
            my_Cmd.Connection = my_Conn;
            my_Cmd.CommandText = "dbo.GetArticlesByPage";
            my_Cmd.CommandType = CommandType.StoredProcedure;

            // Add parameter 0
            SqlParameter my_Parm0 = new SqlParameter();
            my_Parm0.ParameterName = "@Tvp";
            my_Parm0.SqlDbType = SqlDbType.Structured;
            my_Parm0.Direction = ParameterDirection.Input;
            my_Parm0.Value = my_Table;
            my_Cmd.Parameters.Add(my_Parm0);

            // Add parameter 1
            SqlParameter my_Parm1 = new SqlParameter();
            my_Parm1.ParameterName = "@PageIndex";
            my_Parm1.SqlDbType = SqlDbType.Int;
            my_Parm1.Direction = ParameterDirection.Input;
            my_Parm1.Value = 2;
            my_Cmd.Parameters.Add(my_Parm1);

            // Add parameter 2
            SqlParameter my_Parm2 = new SqlParameter();
            my_Parm2.ParameterName = "@PageSize";
            my_Parm2.SqlDbType = SqlDbType.Int;
            my_Parm2.Direction = ParameterDirection.Input;
            my_Parm2.Value = 5;
            my_Cmd.Parameters.Add(my_Parm2);

            // Add parameter 3
            SqlParameter my_Parm3 = new SqlParameter();
            my_Parm3.ParameterName = "@PageCount";
            my_Parm3.SqlDbType = SqlDbType.Int;
            my_Parm3.Direction = ParameterDirection.Output;
            my_Parm3.Value = 5;
            my_Cmd.Parameters.Add(my_Parm3);

            // Open the connection
            my_Conn.Open();

            // Debug info
            Console.WriteLine("Test - execute reader");

            // Execute the reader
            SqlDataReader my_Reader = my_Cmd.ExecuteReader();
            if (my_Reader.HasRows)
            {
                while (my_Reader.Read())
                {
                    Console.WriteLine("{0}", my_Reader[0].ToString());
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }

            // Close the reader
            my_Reader.Close();

            // Number of pages (output after reader - order is important)
            Console.WriteLine("Pages = ");
            Console.WriteLine(my_Cmd.Parameters["@PageCount"].Value.ToString());

            // Close the connection
            my_Conn.Close();

            // Debug info
            Console.WriteLine("Test - close connection");

            // Debug info
            Console.WriteLine("Test - End");

            // Pause to view output
            Console.Read();
        }
    }
}

Here is a snapshot of the correct output from the C# console application.

I have to thank you for your question!

It has been a while since I coded in C#. But like a bike, does not take long to get back on it. The T-SQL examples were done with SSMS 2012 and the C# program was done with VS 2013. The latest and greatest.

Good nite!

Answer 3

I make no claim about efficient or correct -- but readable modern syntax your base query can be written like this:

SELECT ROW_NUMBER() OVER (ORDER BY [dateadded]) AS RowNumber,[desid]
INTO #Results
FROM [DB_user1212].[dbo].[discussions] as d 
JOIN [DB_user1212].[dbo].[CategoryMap] as c ON d.desid=c.[Topic Id] 
JOIN [dbo].[CategoryIdArray] arr ON c.[Category Id] = arr.CategoryID
WHERE [TopicType]='1'