Skip to content

Instantly share code, notes, and snippets.

@tekiegirl
Created October 28, 2013 17:16
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save tekiegirl/7200812 to your computer and use it in GitHub Desktop.
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
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