How to use one sp's result in my new Sp

pashitech

Hi, I have an sp name Movie_SP which returns all the movies by taking MovieName Now in my New sp(Search_Movies_SP) I want to use the out put of the first SP(Movie_SP) and with the result of Movie_SP I have to manipulate Actually I need to set priorities as my first SP Movie_SP is returning all the movies with say BLACK in an Improper way I want to make it to a order where Black comes First, then The Movies which starts with Black second, and the third is The Movies which contains Black Plz help me thanks Pashi

Joe 2

Could've been better if you have mentioned which database you are working with.Assuming that you are working with Sql Server...

pashitech wrote:

use the out put of the first SP

Are you looking out for something like an Output Parameter[^]? If no (which I assume so), I guess you'll have to populate the data onto a temporary table for manipulations.

pashitech wrote:

make it to a order

You may try using the LIKE[^]operator.

Select * from tblMovies where MovieName = 'Black'

The above query fetches you details where the movie name is black (an exact match)

Select * from tblMovies where MovieName Like 'Black%'

The above query gives you details where the movie name begins with black (including black)

Select * from tblMovies where MovieName like '%Black%'

This one fetches movie names that contain the word black. -- For better usage with the like operator, refer to the URL linked.

andyharman

How about:

create table #temp1 (
  --list of columns returned by Movie_SP goes here
  )
insert into #temp1
  exec Movie_SP @Param1, Param2, ...
select * from #temp1
  order by case
    when Title = 'Black' then 'A'
    when Title LIKE 'Black %' then 'B'
    else 'C'
    end,
    Title

You can use "insert into" to return the resultset of a stored procedure into a temporary table. The following select statement uses a case statement to prioritorise the ordering. You might need to tweak this a bit because I didn't quite understand your requirements. Regards Andy