storing records in temp table in sql server and using LINQ in C# to access these records

I am using LINQ with c# and pulling certain questions (say 25) from a table randomly and need to store these questions some where in database say in a temp table. Now on every next or previous button clicks I need records from this temp table. On finish button click i need to drop this table. I created one stored procedure, used select into statement and filled this temp table. Second stored procedure to pull the values from this sp but I got an error reading through this stored procedure. Any ideas how can we fix the following error or is there better way of dealing this issue

Error: Stored procedure returning integer instead of ISingleResult

I also tried creating a table variable as mentioned on this site  but I can't access this Table variable in other stored procedures on next previous clicks

Any ideas
Who is Participating?
teebonConnect With a Mentor Product ManagerCommented:
Why don't you pull out the random questions and store in a session variable instead?
Fernando SotoRetiredCommented:

Can you post the Stored Procedure.
mmalik15Author Commented:
sure following is my sp
ALTER PROCEDURE [dbo].[sp_GetRandomQuestionsAndStoreInTempTable]
	-- Add the parameters for the stored procedure here
	@QuizID varchar(500)
Create table #UserQuestionstmp 
QuestionID int,
Question varchar(8000),
Choices varchar(8000),
CorrectAnswer varchar(5000),
RelatedInfo varchar(8000),
QuestionType varchar(50),
Section varchar(5000),
QuestionNo int

INSERT INTO #UserQuestionstmp
exec dbo.sp_GetQuestionsByChapterID @QuizID


Open in new window

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

mmalik15Author Commented:
And the error I am getting is

The return types for the following stored procedures could not be detected. Set the return type for each stored procedure in the Properties window.
Fernando SotoRetiredCommented:

Are you sure that this is the correct SP. I am not a SQL Guru but it looks like to be it creates a table and insert record into it?
mmalik15Author Commented:
After inserting recrods in temp table i want to pull records on subsequent requests from this table(don't know if thats possible). The reason i am doing this is becasue the sp below generates random records and after generating these records I need to save them some where to access them during the user's session quiz

ALTER PROCEDURE [dbo].[sp_GetQuestionsByChapterID]
      -- Add the parameters for the stored procedure here
      @QuizID varchar(5000)

    -- Insert statements for procedure here

select top 24 QuestionID,Question, Choices,CorrectAnswer,RelatedInfo,QuestionType, Section, row_number() over(order by QuestionNo) AS QuestionNo  from (
Select Q.QuestionID,Q.Question,Q.Choices,Q.CorrectAnswer,
Q.RelatedInfo,Q.QuestionType,Q.Section, newid() QuestionNo
FROM dbo.Questions Q
where  (CHARINDEX( ',' + convert(varchar,Q.Chapter) + ',', ',' + @QuizID + ',' ) > 0)
) x
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.