Selecting from the result set of a stored procedure?

Spinlock


Is there any way one can "select * from (exec sp_MyStoredProc @var='someval') where SomeCol='someotherval'" from a single statement I have tried using functions, but neither of the 3 types are sufficient: I need to return a table / rowset, the source statement involves multiple lines and ugly dynamic SQL, and I do not know what columns I will be returning until half way through execution.

For some context, I am trying to create a central security mechanism for our views used in reporting. In the RDL's the dataset SQL is in the form of 'select * from XRV_ViewName where UserID=@UserID'. I've made a stored procedure that takes a view name and some security credentials which analyzes the view's columns and generates a large select statement with appropriate where clauses appended to it. Obviously, all I want to do now is somehow return the results of that select to the RDL. Just doing exec(thesql) in the stored proc works fine in management studio, but it's painfully apparent that you can't directly select from the results of a stored proc in a single statement. Functions seem insufficient as well for previously mentioned reasons. I've can't make output cursor variables work (in a single line), I've researched temp tables and table variables, even looked at openquery and I can't seem to find any decent solution. Any help or direction would be greatly appreciated!

Bhudev

Hi

It can be possible using OPENROWSET() See Help..giving sample...but check whether syntax is ok.

SELECT * FROM OPENROWSET('SQLOLEDB','server=name';trusted_connection=yes;'exec procedue parematers')

For that U have to make activation of Ad hoc queries in SQL 2005.

ManiD

You can't direclty fetch the result from the SP..

But there is few work around available.. the OPENROWSET is one of the workaround, but you can cosider the following approach too..

1. Geting the SP result in Tables

Create Table #Temp (SomeColumn Definition)

Insert INTO #Temp Exec spName @Params

Select * From #TEMP

2. Convert your SP logic into Function .. (RECOMMENDED)

Create Function db.FetchData(@UserID)

Returns @ResultTable Table (SomeColumn Definitions..) as

Begin

  Insert Into @Resulttable

   Select .... Where UserID = @UserId;

 Return;

End

go

Select * From FetchData(100);