Stored Procedure within a stored procedure

• ddamalas

  Grasshopper

  Points: 11

  More actions

  July 9, 2001 at 3:47 pm

  #77218

  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,

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  July 9, 2001 at 8:02 pm

  #416977

  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

  Andy
  SQLAndy - My Blog!
  Connect with me on LinkedIn
  Follow me on Twitter

• don1941

  SSCarpal Tunnel

  Points: 4183

  More actions

  December 8, 2002 at 3:39 pm

  #443613

  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