July 9, 2001 at 3:47 pm
Hello..
I was trying to write a stored procedure that could execute another row-returning stored procedure and apply a where and order by clause to be used with some components I wrote.
I'm probably not explaining myself properly, but I imagine that the stored procedure would look something like:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE procedure sp_execute_sp
(
@procedure_name varchar(100)
,@where_clause varchar(1000)
,@order_by_clause varchar(1000)
)
as
... sql ....
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Unfortunately, it seems to be harder than I thought. We're using sql 7 and the hard part is getting the results of the named stored procedure into a temporary table. It's difficult because the columns may vary from stored procedure to stored procedure.
Any thoughts?
Thanks,
July 9, 2001 at 8:02 pm
You cant use the results of a stored proc directly in TSQL (ADO is often a better choice for these types of things). The easiest way to do it in TSQL is like this:
Insert into #Temp exec your_stored_procedure
Look hard at your reasons for doing this - if it's to keep your data access code separate, good. If you're seeking performance, you lose a lot of your hoped for gains if you start doing a lot of dynamic sql and stuff - recompiles hurt.
Andy
December 8, 2002 at 3:39 pm
A common problem. It's too bad table-valued user-defined functions can't be created that return the results of store procedure.
As for parametering the ORDER By clause, there is a neat case statement that allows you do choose which column you want to sort by
Goes something like this
select *
from table
order by
case
when @OrderBy = 1 then ColumnA end,
when @OrderBy = 2 then Columnb end
end
when
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply