Solved

How to use temp table in Stored Procedure with LINQ to SQL

Posted on 2011-09-09
9
2,104 Views
Last Modified: 2013-11-11
I am selecting certain records from a table randomly and saving them in a temp table. I have created a stored procedure to select into this table and need to pull these randomly generated records in different parts of my c# code . Another stored procedure to pull the values from this sp later on. While trying to run the first stored procedure i am getting this error  

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

Any ideas how to fix this error or a better approach to handle this situation?

I have also tried creating a table variable as mentioned on this site http://odetocode.com/code/365.aspx  but I can't access this Table variable in second stored procedures in parts of code


0
Comment
Question by:mmalik15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 36516184
You're getting that error because the table does not exist when LINQ tries to determine what gets returned.  It can be avoided by not using an actual temporary table but a real one instead.  If you call it something like TmpTable or Temp.Table (using s special "temporary" schema), you would avoid that error and your DB would still remain fairly clean.

Another option is editing the DMBL as can be read here: http://riteshkk2000.blogspot.com/2010/08/error-unknown-return-type-return-types.html
But I'm not familiar with that myself so can't say more about it...

Hope that helps you further a bit?
0
 

Author Comment

by:mmalik15
ID: 36516255
thanks for the comment valentionV.

How can I use a real temp table schema. Can you give me an example of it? All I need is temp table for storing certain questions and keep the data until the the user clicks on finish button. And there can be concurrent users too for this application, each having their own set of questions.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 36518905
Sorry if I wasn't clear.  What I meant is that you can create a new schema called Temp for instance.  In that schema you would create a table that has the same layout as your current temporary table.  This Temp.SomeTable would then be used as a "temporary" table, in the meaning that the data that it contains is temporary, but the table itself is not.

As for concurrent users, you could create an additional column in your "temporary" table to indicate what user the record belongs to.

Note that you'll need some sort of cleanup process as well.  When would records become obsolete?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:mmalik15
ID: 36518925
What you're saying is to create a temp table in the database just like any other table and insert randomly picked questions in it along with the userinfo? And delete the records from that table when the user clicks finish test button from that table?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 36518936
That's right, but it's not a "temp table", it's a table just like any other.  Based on the info you've given, it seems like what you're needing to deal with is some sort of session state.  What happens if the user taking the test closes the application by accident, or is faced with a crash?  I think it would be beneficial if that user would be able to continue the test from the point where it ended.  And that should be possible using the table as I've described it.  On the other hand, it would be a problem with the temporary table that you were thinking to use.
0
 

Author Comment

by:mmalik15
ID: 36518943
it sounds like a good idea but yes the problem can arise when the user has accidently closed the browser without clicking the finish button when we clear records from this table
0
 

Author Comment

by:mmalik15
ID: 36518957
I dont want the user to resume quiz if he/she has closed the browser rather the user has to start the test again. And I only want to save the results when the user has clicked on finish button
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 36519189
If you keep a timestamp in the table as well, that should help you to clean out the outdated records.  You could run a nightly job that deletes every record older than 24 hours for instance, thereby deleting the "orphaned" records.
0
 

Author Closing Comment

by:mmalik15
ID: 36596210
sorry for late reply. thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question