R – How to get data from a SubSonic stored procedure

stored-proceduressubsonic

I have a stored procedure:

CREATE PROCEDURE [dbo].[usp_SelectStuff] AS

@param1 int
@param2 int

BEGIN

SELECT [Stuff] FROM TABLE

END

I want to run this from SubSonic, and the use the stuff that has been selected. So I do:

var db = DB.CreateDB();
var stuffProcedure = db.UspSelectStuff(0,1);

Now how do I actually get the data from here?

Best Answer

You can also load a collection and match properties will be populated:

ProductCollection coll = new ProductCollection();
coll.LoadAndCloseReader(db.UspSelectStuff(0, 1).GetReader());

Related Solutions

Sql-server – Select columns from result set of stored procedure

Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable.

Declare @tablevar table(col1 col1Type,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar

Sql – Insert results of a stored procedure into a temporary table

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

Best Answer

Related Solutions

Sql-server – Select columns from result set of stored procedure

Sql – Insert results of a stored procedure into a temporary table

Related Topic