Can you run a select Statement on a stored Procedure.

[Stunner01225]

I have a very complicated SQL Statement, but can I run a select Statement on a stored Procedure.?

 

[donutman]

No, but if it has no variables, then you can make it a view and use the view in a select statement. You can execute a SP however.
Try: Exec yourStoredProc parametersIfAny
-Karl

 

[samshiell]

Nope.

Can you turn your proc into a view? if so you can run a select on that.

You could input the results of your proc into a temporary table then run a select on that i.e.

<first create a temp table that's the correct structure>

INSERT #Temptable
EXEC usp_MyProc


Note that you can't do this with a table variable, it has to be a "real" temporary table.

Final option is to have your proc accept optional parameters with the select criteria..

Any use??

Sam

 

[Kris11]

How about using a funciton? If you move your code from the Procedure to a function then you can do a SELECT on it, same like you do with a table. So it would be like

SELECT * from <your_function> ( <Give all arguments here> )
	WHERE <field_name> = <value>

Hope I answered your question.

-Kris

 

[snag56]

Another convoluted approach would be to establish a linked server definition back to itself (the same server).
Then you can do an OPENQUERY to run your sp.
The mainline query will them treat the sp results as a temp table, to which you can JOIN, as necessary.

However, I wouldn't do this with large sp's producing large resultsets,
as it is very inefficient. I have also successfully used OPENQUERY to JOIN to DBCC output, such as DBCC INPUTBUFFER.

 

[samshiell]

snag56

Wow, there's obscure solutions and there's obscure solutions! I'd hate to see what your dreams are like.

Sam