The Situation
I recently wanted to declare a table, insert data into it and then, depending on a variable passed in to the stored procedure (@i_bitCurrent), either return the contents of the table, or filter the contents with a Where clause and return them (3 options in total). The reason I wanted to use the temporary table approach was to avoid duplicating the long select statement 3 times, with just the Where part of the statement being different (or have three separate stored procedures).
The Problem
I got the stored procedure to work, and return the expected data set when run from within Microsoft SQL Management Studio, but Entity Framework (in Visual Studio 2012) refused to recognise the return type (it saw a return type of nullable int).
The Solution
After Googling the problem I found a similar problem and suggested answer on Stack Overflow – I needed to supply a faux data contract that Entity Framework would then use as the return type. The faux data contract is the first IF statement in the code below.
The Code
This is a cut-down version of the code, so you can see why I did not want to duplicate it (making changes would ahve been a nightmare…) The faux data contract IF statement at the beginning will never be executed as 1 will never equal 2. It is simply there to get Entity Framework to recognise the return type. It will need to be updated if the temporary table is ever updated.
IF 1 = 2
BEGIN
SELECT
cast(null as int) as UserId
,cast(null as nvarchar(250)) as Username
,cast(null as int) as RoleCount
,cast(null as datetime) as UserRevoked
,cast(null as bit) as Locked
,cast(null as bit) as ForcePassChange
,cast(null as varchar(200)) as ClientName
WHERE
1 = 2
END
CREATE table #tmpResults (
UserId int,
Username nvarchar(250),
RoleCount int,
UserRevoked datetime,
Locked bit,
ForcePassChange bit,
ClientName varchar(200)
)
INSERT #tmpResults (
UserId,
Username,
RoleCount,
UserRevoked,
Locked,
ForcePassChange,
ClientName
)
SELECT
u.UserId,
u.Username,
(SELECT COUNT(*)
FROM USERROLE
WHERE FKUserId = u.UserId AND Revoked IS NULL),
u.Revoked,
u.Locked,
u.ForcePassChange,
c.Name
FROM [USER] u LEFT OUTER JOIN [CLIENTS].[dbo].[CLIENT] c on u.FkClientId = c.ID
ORDER BY u.UserId
IF(@i_bitCurrent IS NULL)
BEGIN
SELECT * FROM #tmpResults
END
IF(@i_bitCurrent = 1)
BEGIN
SELECT * FROM #tmpResults
WHERE (UserRevoked IS NULL OR UserRevoked > GetDate())
ORDER BY UserId
END
IF(@i_bitCurrent = 0)
BEGIN
SELECT * FROM #tmpResults
WHERE (UserRevoked IS NOT NULL AND UserRevoked < GetDate())
ORDER BY UserId
END
Entity Framework / Visual Studio
Then when I removed the stored procedure from the model in Visual Studio (from both the Stored Procedures / Functions folder and Function Imports folder), and refreshed the model from the database, the stored procedure was added correctly and a Complex Type created with the correct return value.
That is an Entity Framework Hack!
Note
Ints and bits (bools) will be returned as nullable, so you may need to use returnType.fieldName.Value ( in C#, or the equivalent in the language you are using) to access the value of these types in the returned type.
Jacqui's Technical Journal 