mercredi 21 juillet 2021

The selected store procedure or functions returns no columns

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 sprocedure 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 appriciated

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 store 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



Aucun commentaire:

Enregistrer un commentaire