CODEWITHSUNDEEP
t-sqlstored-proceduresentity-framework-6sql-server-2014
diiN__________
diiN__________

Reputation: 7656

The selected stored procedure or function returns no columns

I've searched in the internet and found similar posts about it. However, no solution worked for me.

I'm facing an issue with one of my stored procedures. When adding it to my project in Visual Studio I get the following message:

The selected stored procedure or function returns no columns.

Earlier I could solve the problem by adding SET FMTONLY OFF in my procedure (as answered here). Now this doesn't change anything; it still returns no columns according to EF.

My stored procedure looks like this:

ALTER PROCEDURE [dbo].[spImportData]
    @someParameter
AS
BEGIN
    SET NOCOUNT ON;

    -- Some insertion
    INSERT INTO dbo.SomeTable ([SomeField])
    SELECT @someParameter

    SELECT CAST(SCOPE_IDENTITY() AS BIGINT) AS ImportId
END

Same result when using a temp table:

CREATE TABLE #temp (ImportId BIGINT)
INSERT INTO #temp (ImportId)
SELECT CAST(SCOPE_IDENTITY() AS BIGINT)
SELECT ImportId FROM #temp
DROP TABLE #temp

Still no columns with a table variable:

DECLARE @importId BIGINT = SELECT CAST(SCOPE_IDENTITY() AS BIGINT)
SELECT @importId

When executing the procedure directly in SQL Server, I'm getting the desired value.

What am I missing? I thought SET FMTONLY OFF would do the trick but in this case nothing changes and my procedure still "returns no columns".

Edit 1

I tried what @NEER suggested in his comment. I changed the SELECT to SELECT 1 AS ImportId. But I'm still getting the same message.

Upvotes: 0

Views: 1285

Answers (1)

diiN__________
diiN__________

Reputation: 7656

I've found the issue. I've added SET FMTONLY OFF on top of the inner block of my stored procedure and above the declaration because one of them alone didn't work. Neither did both of them together... So I removed the one inside the block and tried it again what surprisingly worked as expected.

My stored procedure now looks like this:

SET FMTONLY OFF
GO
ALTER PROCEDURE [dbo].[spImportData]
    @someParameter
AS
BEGIN
    -- SET FMTONLY OFF; -> I removed this line

    SET NOCOUNT ON;

    -- Some insertion
    INSERT INTO dbo.SomeTable ([SomeField])
    SELECT @someParameter

    SELECT CAST(SCOPE_IDENTITY() AS BIGINT) AS ImportId
END

Upvotes: 1

Related Questions