none
The selected stored procedure returns no columns Stored procedure Function import RRS feed

  • Question

  • I have this stored procedure given to me to use in the entity framework to display on data grid. How can I do this in the Entity framework Function import? dynamic Stored proc has 2 inputs and output parameters, the out of the stored procedure gives different results with change of inputs.

    ALTER PROCEDURE [dbo].[GetPatientImplantableDevice]  
    (
    @PatientKey NVARCHAR(38),
    @ParPatientImplantableDeviceCategoryId SMALLINT
    )
    AS

    BEGIN
    SET NOCOUNT ON;
    SET FMTONLY OFF;
    DECLARE @COUNT INT
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @VISIBLE BIT
    DECLARE @ColumnId SmallInt
    DECLARE @COLUMN NVARCHAR(100)
    DECLARE @LocalColumn NVARCHAR(100)

    --select * from PatientImplantedDevice;

    DECLARE @ImplantedColumns TABLE
    (SortOrder SmallInt, VISIBLE BIT, ColumnName NVARCHAR(100), LocalizedColumnName NVARCHAR(100), ColumnId Int Primary Key)

    --Get the localized column names for PatientImplantedDevice and whether they are invisible or not
    INSERT INTO @ImplantedColumns 
    SELECT PICV.SortOrder,
    PICV.Visible, 
    PIC.PatientImplantedColumnName, 
    PICL.LocalPatientImplantedColumn, 
    PIC.PatientImplantedColumnId 
    FROM PatientImplantedColumn PIC
    INNER JOIN PatientImplantedColumnVisible PICV ON PIC.PatientImplantedColumnId = PICV.PatientImplantedColumnId AND PICV.Visible = 1
    INNER JOIN LanPatientImplantedColumn PICL ON PIC.PatientImplantedColumnId = PICL.PatientImplantedColumnId
    WHERE PICV.PatientImplantedDeviceCategoryId = @ParPatientImplantableDeviceCategoryId 

    --Build a select statement with the localized column names
    SELECT @COUNT = COUNT(*) FROM @ImplantedColumns

    IF @COUNT = 0 
    RETURN

    SET @SQL = 'SELECT '

    WHILE @COUNT > 0
    BEGIN

    SELECT TOP 1 @Column = ColumnName, @LocalColumn = LocalizedColumnName, @Visible = Visible, @ColumnId = ColumnId FROM @ImplantedColumns Order By SortOrder

    SELECT @SQL = @SQL + @COLUMN + ' AS [' + ISNULL(@LocalColumn,'') + '], '

    SET @COUNT = @COUNT - 1
    DELETE @ImplantedColumns WHERE ColumnId = @ColumnId

    END

    --Add the count of records for that category
    SELECT @SQL = @SQL + ' (SELECT COUNT(PatientImplantedDeviceCategoryId)
               FROM  dbo.PatientImplantedDevice
               WHERE (PatientImplantedDeviceCategoryId = PID.PatientImplantedDeviceCategoryId) AND (PatientKey = PID.PatientKey)
               GROUP BY PatientImplantedDeviceCategoryId) AS DeviceCategoryCount'

    --Filter by category and patient  
    SELECT @SQL = @SQL + ' FROM PatientImplantedDevice PID WHERE PatientImplantedDeviceCategoryId = ' + CAST(@ParPatientImplantableDeviceCategoryId AS VARCHAR(2))
     + ' AND PatientKey = ' + '''' + @PatientKey + ''''; 

    --Exec to get back only columns that the user wants visible using the localized column names.
    EXEC SP_EXECUTESQL @SQL, N'@ParPatientImplantableDeviceCategoryId INT, @PatientKey NVARCHAR(38)',@ParPatientImplantableDeviceCategoryId,@PatientKey
    select * from PatientImplantedDevice;
    END

    Friday, August 3, 2012 6:14 PM

Answers

  • Hi SuneethaISI,

    Welcome to MSDN Forum.

    I'm afraid Entity Framework doesn't support dynamic results of stored procedure, please refer to this thread. The Answer from Jacky-Sun list the limitation of stored procedure in Entity Framework, please refer to it.

    Based on this issue, I suggest you to execute stored procedure in Entity Framework directly, here's a document about how to execute in Entity Framework directly, please refer here.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, August 6, 2012 2:26 AM
    Moderator

All replies

  • Hi SuneethaISI,

    Welcome to MSDN Forum.

    I'm afraid Entity Framework doesn't support dynamic results of stored procedure, please refer to this thread. The Answer from Jacky-Sun list the limitation of stored procedure in Entity Framework, please refer to it.

    Based on this issue, I suggest you to execute stored procedure in Entity Framework directly, here's a document about how to execute in Entity Framework directly, please refer here.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, August 6, 2012 2:26 AM
    Moderator
  • Thank you for the response atleast I know we cannot it. I will look into the links you sent.

    I really appreciate your response

    Thanks


    Suneetha Srinivasa

    Monday, August 6, 2012 3:10 PM