无效的对象名称'dbo.CategoryIdArray' [英] Invalid object name 'dbo.CategoryIdArray'

问题描述

My Motive是将长数组ID作为参数传递给存储过程，并根据ID选择数据。所以我创建了SQL Server中的类型

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;

  }

当我在上面运行时，抛出了无效的对象名称'dbo.CategoryIdArray'。但我创建它作为类型。帮帮我解决我错过的问题。 如何将数组传递给我SQL SERVER 2008存储过程并将数组的所有值插入表中[ ^ ]

推荐答案

在你的SP ...

See this in your SP...

(select CategoryId from [dbo].[CategoryIdArray])

您无法从类型中选择这个

You can not select from a type you probably ment this

(select CategoryId from @dt)

这篇关于无效的对象名称'dbo.CategoryIdArray'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！