Created
October 28, 2013 17:16
-
-
Save tekiegirl/7200812 to your computer and use it in GitHub Desktop.
Getting Entity Framework to recognise the return type of a Stored Procedure that returns a #temp table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
http://jacstech.wordpress.com/2013/09/27/getting-entity-framework-to-recognise-the-return-type-of-a-stored-procedure-that-returns-a-temp-table/