CODEWITHSUNDEEP
c#asp.netsql-server
ONYX
ONYX

Reputation: 5859

SQL Return value

How do i return a table of a type. I want to return the table @Forums but i get an error Must declare scalar vaiable @Forums. My code return nothing after i imported a stored procedure function into my 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;
            }
        }
    }
}

Upvotes: 0

Views: 386

Answers (2)

RickNZ
RickNZ

Reputation: 18654

You can't return a "table" from a stored procedure. The RETURN value can only be a single number (a scalar).

To return multiple rows from a stored procedure, you simply execute a SELECT that returns rows (as opposed to one that makes variable assignments). The rows from the SELECT are returned to the client in the form of a "result set", and are available from a reader.

So, to fix the code in your question, remove the table variable declaration, the INSERT statement and the RETURN statement, and just execute the SELECT call.

FWIW, you can also return multiple result sets from stored procedures, simply by executing multiple SELECTs that return rows.

Upvotes: 0

Tim M.
Tim M.

Reputation: 54359

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;

I don't see any reason you need a variable at all; you are selecting a combination of real and calculated columns which is legal.

For example, I could say SELECT X = 1 FROM Forums; The "X" column doesn't come from the Forums table, but it is valid.

If for some reason you do want to use the table variable:

SELECT * FROM @Forums;

Lastly, the error message you received indicates the cause of your original problem:

Must declare scalar variable

A table is not a scalar value. The actual return value of a stored procedure must be scalar (obviously the stored procedure is allowed to SELECT any data it wants, but this is different from its return value).

Upvotes: 2

Related Questions