What happens in EXEC, stays in EXEC. Lifespan of a MSSQL table variable

One of my all time favorite features of MSSQL 2005+ is being able to create table variables on the fly from SELECT statements. This isn’t a lesson in what table variables are, but here is an easy sample in case this is a new concept.

Running this query

SELECT * INTO #myTableVar FROM YourTable

Gives you a new table variable named myTableVar. Table variables are scoped to the active connection, so running this will work.

// make table var
SELECT * INTO #myTableVar FROM YourTable
// show me the data
SELECT * FROM #myTableVar
// you can drop it if you wish
DROP TABLE #myTableVar

However, let’s say you have an aspx page or a sproc that runs this query.

SELECT * INTO #myTableVar FROM YourTable

You can not access myTableVar in a separate connection to the database because as soon as the first query’s connection closes, myTableVar gets dropped.   Here are a few other scenarios that also demonstrate the scoping of a table variable.

-- FAILS
EXEC ('SELECT * INTO #tmp FROM MyTable;');
-- #tmp does not exist
SELECT * FROM #tmp
#tmp only exists inside of EXEC

Table variable #tmp lives inside of EXEC

Here we see that the table variable #tmp only lives for the life of the statement inside of EXEC. The second SELECT * calls is outside of the EXEC statement.

-- #tmp2 works inside of EXEC statement
EXEC ('SELECT * INTO #tmp2 FROM MyTable; SELECT * FROM #tmp2');
table variables in EXEC live in EXEC

What happens in EXEC, stays in EXEC

Here #tmp2 works because it’s being used inside of the EXEC statement. This is worth knowing if you work with dynamic sql statements and exec.

-- works!
SELECT * INTO #tmp FROM MyTable;
-- #tmp exists
SELECT * FROM #tmp
typical sample of using mssql table variable

typical sample of using mssql table variable

This is a typical example that you may use inside a sproc, trigger, script, etc. Both sql calls live in the same space, so #tmp exists.

How to JOIN two tables using LINQ to SQL

Wanted to share this since it gave me so much trouble figuring out.  It’s a simple SQL query ported to LINQ to SQL that joins two tables to return a filtered listed of data.

Here are the tables from my schema
user, user_video, video tables

Here is a basic SQL statement I could fire to retrieve my user videos.

select  *
from    video v, user_videos uv
where   v.vid = uv.vid
and     uv.uid = 2

User 2 has two videos
Here is how you would run the same query using .net’s LINQ to SQL.

// create DB connection
var db = new DBCONN();
// run query
List<video> uvids = (
    from c in db.video
    join o in db.user_videos
    on c.vid equals o.vid
    where o.uid == 2
    select c
).ToList();

This query differs slightly from the screenshot below because I used it in a WCF Service.

Same data, different retrieval method

The variable DBCONN is my database connection that I established when mapping my DB.  If you are not familiar with how to set this up, use the Visual Studio’s “Add the ADO.NET Entity Data Model” wizard.  With your .net project open, right click your project, left click on “Add the ADO.NET Entity Data Model”.  This wizard will walk you through setting up everything you need to setup your DB model file ( edmx ), as well as setting up your database connection and saving it in web.config.

Jesse Liberty did a simple tutorial that uses this wizard in a WCF service application.

I hope this helps somebody out.

Come have a 360Flex chat with me and Jun Heider

Chat is @ Thursday Feb 11th, 11:30am MST

Jun Heider and myself will be talking to the 360|Flex guys tomorrow about our session.  It’s actually going to be a back-to-back mega session comparing the latest and greatest on both the Flash Platform and the Silverlight Platform.

Come check it out and feel free to ask questions…although for the good stuff you’ll have to wait until our talks. ;-)

Here’s the full details: http://www.360flex.com/blog/2010/02/360flex-speaker-chat-eric-fickes-and-jun-heider/

I hope to see you online tomorrow or at 360 Flex in March.



I'm speaking at 360 Flex 2010

I'm speaking at 360 Flex 2010

Invalid token ‘void’ in class, struct, or interface member declaration

EDIT :

After finishing this post I ran into all sorts of other strange issues and restarted using a Web Appliction instead of a plain old Website.  Between IntelliSense not showing any classes, to project reference issues, I couldn’t figured it out in time.  I’m sure there’s a way, I just had to move on.  So maybe this ramble below will be helpful for someone.

EF

Just ran into something quirky with Visual Studio 2008’s new ADO.NET Entity Data Model wizard.  While working on an ASP.NET 3.5 website ( not a codebehind web application ) I was trying to get the ADO.NET Entity Data model wizard to work with MySQL and ran into a probable Visual Studio bug.  To sum up the issue, if you are going to add a new edmx to your project, do NOT save it to the App_Code folder initially.  Put it in your root folder, compile your project, then move the edmx where you’d like.

Assuming you’ve already created your ASP.NET Website project, here’s how you reproduce this issue.

Right click your project and left click ‘Add New Item’

Right click your project, left click Add New Item

Add New Item

Select ADO.NET Entity Data Model, name it, select your language of preference

New ADO.NET Entity Data Model

ADO.NET Entity Data Model

Click Yes to the ‘Store in App_Code’ prompt

Place your edmx in App_Code folder

Place file in 'App_Code' folder

Complete the new Entity Data Model wizard

See this tutorial if you have not done this before

Compile project after completing wizard

Invalid token 'void' in class, struct, or interface member declaration

Invalid token 'void' in class, struct, or interface member declaration

At this point your project should have a new.edmx file located inside of the App_Code folder, but the project won’t build without failing.  If you are stuck in this predicament, follow this workaround.

Move .edmx to root folder and rebuild

Move edmx to root and recompile, no errors!

WORKAROUND : move edmx to root folder, then recompile

After moving your edmx file to the root folder you should be able to compile without problem.  Assuming this solves your problem, you should be able to move your edmx file to the App_Code folder without problem.  Seems like an initial compile problem.

.