I'm trying to call the stored procedure attached below using Entity Framework 4.1 to cover 2 possible scenarios:
exec TEST_SP_OUTPUT 1 Should return a dataset from ACTIONTYPE table and @Success = 1
exec TEST_SP_OUTPUT 0 No dataset returned and @Success = 0
CREATE PROCEDURE [dbo].[TEST_SP_OUTPUT] ( @Id int, @Success int OUTPUT ) AS BEGIN SET NOCOUNT ON; IF (@Id = 1) BEGIN SELECT ActionName, ActionType FROM ACTIONTYPE SET @Success = 1; END ELSE SET @Success = 0; END
The question is: can I handle both executions using a single Complex Type within the Entity Framework model? Bear in mind that the scenario "exec TEST_SP_OUTPUT 0" is not returning any dataset or columns.
Correct me if I'm wrong, but I a possible solution may be setting dummy results for the second scenario:
CREATE PROCEDURE [dbo].[TEST_SP_OUTPUT]
(
@Id int,
@Success int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
IF (@Id = 1)
BEGIN
SELECT ActionName, ActionType FROM ACTIONTYPE
SET @Success = 1;
END
ELSE
BEGIN
SELECT '' as ActionName, '' as ActionType
SET @Success = 0;
END
END
Cheers.
Juan.