Entity Framework : the selected stored procedure or functions returns no columns

103 Views Asked by At

I am getting this error from Entity Framework while creating a complex type. May of them are telling to user SET FMTONLY OFF but for me it is not working. The stored procedure is returning the column but the columns it returns are not fixed. it sometimes returns a table with 2 columns or 4 columns. I am using Entity Framework 5.0. Any help will be much appreciated

SET FMTONLY OFF
GO

ALTER PROCEDURE [dbo].[sp_hcube_rack] 
    @hcapmaxmodule int
AS   
BEGIN
    DECLARE @col_name varchar(1055)       
    DECLARE @count  int, @cnt int
    DECLARE @col varchar(255), @cmd nvarchar(1000)
    DECLARE @ParmDefinition nvarchar(500);

    DECLARE getinfo CURSOR FOR
        SELECT c.name 
        FROM sys.tables t 
        JOIN sys.columns c ON t.Object_ID = c.Object_ID
        WHERE t.Name = 'Hydrcube_rack_mapping'

    OPEN getinfo
  
    FETCH NEXT FROM getinfo INTO @col

    CREATE TABLE #column_tbl (ID INT, tempUserName varchar(MAX))

    SET @cnt = 1

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @cmd = N'SELECT @count = COUNT(*) 
                        FROM Hydrcube_rack_mapping 
                        WHERE Hcap_modules = ' + CAST(@hcapmaxmodule AS nvarchar(20)) + 
                        ' AND ['+@col+'] IS NULL'

        EXEC sp_executesql @cmd, @ParmDefinition = N'@count INT OUTPUT', @count = @count OUTPUT;

        IF (@count = 0)
        BEGIN
            SET @col_name = CONCAT(@col, ',', @col_name)

            INSERT INTO #column_tbl (ID, tempUserName) 
            VALUES (@cnt, @col_name)

            SET @cnt = @cnt + 1
        END 

        FETCH NEXT FROM getinfo into @col
    END

    CLOSE getinfo
    DEALLOCATE getinfo

    SELECT TOP 1 @col_name = tempUserName 
    FROM #column_tbl 
    ORDER BY ID DESC

    SELECT @cmd = 'SELECT ' + LEFT(@col_name, LEN(@col_name) - 1) +
                  ' FROM Hydrcube_rack_mapping ' + 
                  ' WHERE Hcap_modules = ' + CAST(@hcapmaxmodule AS nvarchar(20)) 
 
    EXEC sp_executesql @cmd
END
GO

Output from the stored procedure:

hcuberack|kit14_1|kit13_1|kit12_1|kit7_2|kit6_2|kit4_3|kit3_4|Hcap_mod|id
24       |1      |1      |1      |1     |1     |0     |0     |24      |24
0

There are 0 best solutions below