Can I filter a stored procedure ?

[ccraw]

Hi, I am new to SQL but have been using MS Access for years.

I currently have a problem where a stored procedure that I have created, needs to be filtered further. I know that you can refer to tables and views in a stored procedure, I was wondering if you can access existing stored procedures through a select statement (or alternate means).

Example Situation: If I had a stored procedure "ShowPalletTbl" with a basic select statement say SELECT PalletTbl.* FROM PalletTbl, could I then point to this stored procedure in a second stored procedure and apply additional WHERE GROUP BY etc statements to get the data that I am after. I know that for this example I could use a view or the table directly, but the trouble I currently have is that I need to further GROUP and filter a current stored procedure.

Thanking any replies in advance.

Colin.

 

[rt63]

You cannot do it directly but what you can do is to create a temporary table in your procedure and populate it by calling the existing procedure and then do the where/group by etc on this temp table

create table #temp (......)

insert into #temp
execute existing_procedure

select ...
from #temp
where ....
group by ...

RT

 

[AWithers]

One solution is to use the stored procedure to populate a temporary table and then apply the filter/grouping to that

e.g.

create table #temptable...

--NB table must have same number /type of fields as the result of your sp

insert #temptable
exec(yoursp)

select * from #temptable
where... etc etc

HTH

Andy

 

[tlbroadbent]

You want to pass selection criteria to the stored procedure a parameters.

Create procedure GetMyRecords
@prm1 varchar(20), @prm2 int, @prm3 char(10)
As

SELECT PalletTbl.*
FROM PalletTb
WHERE Col1=@prm1
Or Col2=@prm2
Or Col3=@prm3
Go

In the calling stored procedure you can pass the parameters like this.

Exec GetMyRecords 'abcdef', 21, '2002-11-26'
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.