SQL Return value

本文关键字：value Return SQL | 更新日期: 2023-09-27 18:22:30

如何返回类型的表。我想返回表@Forums但收到错误 必须声明标量可用@Forums。将存储过程函数导入 edmx 后，我的代码不返回任何内容

DECLARE @Forums Table
 (ForumGroup nvarchar(100), Title nvarchar(100), Description nvarchar(400), 
ThreadCount int, LastPostBy nvarchar(50), LastPostDate datetime, LastPostTtle nvarchar(100))
insert into @Forums
SELECT ForumGroup = (
    CASE WHEN ParentID IS NOT NULL THEN
        (SELECT Title FROM Forums WHERE ForumID = F.ParentID)           
    ELSE
        (SELECT Title FROM Forums WHERE ParentID IS NULL)
    END),
Title, Description, 
ThreadCount = (SELECT COUNT(*) FROM Posts P WHERE  P.ForumID = F.ForumID),
LastPostBy = (SELECT TOP 1 AddedBy FROM Posts P WHERE P.ForumID = F.ForumID ORDER BY P.PostID DESC), 
LastPostDate = (SELECT TOP 1 AddedDate FROM Posts P WHERE P.ForumID = F.ForumID ORDER BY P.PostID DESC),
LastPostTitle = (SELECT TOP 1 Title FROM Posts P WHERE P.ForumID = F.ForumID ORDER BY P.PostID DESC) 
FROM Forums F WHERE ParentID IS NOT NULL
ORDER BY Title
Return @Forums

C#：

public class Forums
{        
    public List<Forum> GetForums()
    {
        using (EntityConnection conn = new EntityConnection("name=CMSEntities"))
        {
            conn.Open();
            EntityCommand cmd = conn.CreateCommand();
            cmd.CommandText = "CMSEntities.sproc_Forums_GetForums";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            using (EntityDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
            {
                List<Forum> forums = new List<Forum>();
                while (reader.Read())
                {
                    Forum forum = new Forum(
                        1,
                        "",
                        DateTime.Now,
                        reader["Title"].ToString(),
                        reader["Description"].ToString(),
                        0,
                        false,
                        null,
                        null,
                        null,
                        true,
                        reader["ForumGroup"].ToString(),
                        1,
                        null,
                        DateTime.Now,
                        null);
                    forums.Add(forum);
                }
                return forums;
            }
        }
    }
}

SQL Return value

SELECT 
    ForumGroup = (
        CASE WHEN ParentID IS NOT NULL THEN
            (SELECT Title FROM Forums WHERE ForumID = F.ParentID)           
        ELSE
            (SELECT Title FROM Forums WHERE ParentID IS NULL)
        END),
    Title, 
    Description, 
    ThreadCount = (SELECT COUNT(*) FROM Posts P WHERE  P.ForumID = F.ForumID),
    LastPostBy = (SELECT TOP 1 AddedBy FROM Posts P WHERE P.ForumID = F.ForumID ORDER BY P.PostID DESC), 
    LastPostDate = (SELECT TOP 1 AddedDate FROM Posts P WHERE P.ForumID = F.ForumID ORDER BY P.PostID DESC),
    LastPostTitle = (SELECT TOP 1 Title FROM Posts P WHERE P.ForumID = F.ForumID ORDER BY P.PostID DESC) 
FROM Forums F WHERE ParentID IS NOT NULL
ORDER BY Title;

我看不出你有任何理由需要一个变量;你正在选择实列和计算列的组合，这是合法的。

例如，我可以说SELECT X = 1 FROM Forums;"X"列不是来自论坛表，但它是有效的。

如果出于某种原因您确实想使用表变量：

SELECT * FROM @Forums;

最后，您收到的错误消息指示原始问题的原因：

必须声明标量变量

表不是标量值。存储过程的实际返回值必须是标量(显然，允许存储过程SELECT它想要的任何数据，但这与其返回值不同(。

不能从存储过程返回"表"。RETURN值只能是一个数字(标量(。

若要从存储过程返回多行，只需执行返回行的SELECT(而不是进行变量赋值的(。SELECT中的行以"结果集"的形式返回到客户端，并且可以从读取器获得。

因此，要修复问题中的代码，请删除表变量声明、INSERT 语句和 RETURN 语句，然后执行 SELECT 调用。

FWIW，您还可以从存储过程返回多个结果集，只需执行多个返回行的 SELECT。

相关文章：