LINQ to SQL Return Type for Execute method in Stored Procedure

Question

I have a stored procedure that builds a dynamic where clause and executes the SQL statement. It looks similar to this:

declare @customerId int
declare @sql varchar(100)

set @customerId = 12
set @sql = Replace('select customerName from customer where customerId = @customerId', '@customerId', @customerId)

exec @sql

I know the above doesn't show a great representation of this as you would never do the above, but hopefully you get the point.

Basically, I want to grab the output of the TSQL within LINQ to SQL. Without moving this entire method to source and off of the DB, is there a way that I can still run an "inline SQL statement" and force it into a particular format (meaning columnA, columnB, etc) as a result type?

This is a hack job to implement functionality into an old application. Also, I'm using LINQ to SQL as other sprocs and table access are available here (old database) and would prefer to leave all data access in one place.

Answer 1

Got it from an email list I'm on. The suggestion is that on SQL 2005 or 2008, I create a table variable and then pass the SQL results into the table variable using the execute sproc. It works.