无效的对象名称'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'。但我创建它作为类型。帮帮我解决我错过的问题。 ]

推荐答案

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

您无法从类型中选择这个

You can not select from a type you probably ment this

(select CategoryId from @dt)

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