Why I get exception when I call stored procedure?

Question

I am using Entity Framework 6 and Microsoft SQL Server 2012.

Here is my stored procedure:

ALTER PROCEDURE SPDeleteRegion
      @siteId int,
      @regionId int 
AS
BEGIN 
    SET NOCOUNT ON;

    DECLARE @isDeleted BIT 

    IF EXISTS (SELECT 1 FROM SiteObjects WHERE SiteRegionId = @regionId)
    BEGIN
        SET @isDeleted = 0 ; --not deleted
        RETURN @isDeleted;
    END
    ELSE
    BEGIN
        --do what needs to be done if not       
        DELETE FROM SiteRegions 
        WHERE Id = @regionId;

        SET @isDeleted = 1;  -- deleted
        RETURN @isDeleted;
    END
END

Here how I call the stored procedure in C#:

var t = _context.Database.SqlQuery<bool>("SPDeleteRegion @siteId, @regionId",
                                          new SqlParameter("@siteId", siteId),
                                          new SqlParameter("@regionId", regionId));

On the line of code above I get this exception:

The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types.

Any idea why I get the excewption and how to fix it?

Why do you specify bool as a type parameter _context.Database.SqlQuery<bool>? Your procedure does not return anything — Adil Mammadov
I am using Oracle. In Oracle if you want to return something from procedure, you have specify it at procedure declaration as output parameter. Is not it true for other databases? You have not declared isDeleted at procedure declaration. — Adil Mammadov
The return value of a stored procedure in SQL Server is always an INT (or bigint) - typically it signals how many rows were affected by an INSERT, UPDATE or DELETE operation. You cannot send back a bit using the RETURN keyword - you need to use either an OUTPUT parameter, or you need to select your bit and thus return a result set — marc_s

amiry jd amiry jd · Accepted Answer · 2016-08-23T10:40:13

Your procedure doesn't selecting anything. Change it like this:

ALTER PROCEDURE SPDeleteRegion
    -- Add the parameters for the stored procedure here
      @siteId int,
      @regionId int 
AS
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE 
    @isDeleted BIT 

    IF EXISTS (SELECT 1 FROM SiteObjects WHERE SiteRegionId = @regionId)
    BEGIN
        SET @isDeleted = 0 ; --not deleted
        SELECT @isDeleted [IsDeleted]; --HERE
    END
    ELSE
    BEGIN
        --do what needs to be done if not       
        DELETE FROM SiteRegions WHERE Id = @regionId;
        SET @isDeleted = 1;--deleted
        SELECT @isDeleted [IsDeleted]; -- AND HERE
    END
END

Why I get exception when I call stored procedure?

1 Answers