Can I use a Where clause in stored procedure

[sfriedman451]

There are some great stored procdures that permit me to get meta data about a database. Is there some way that I can filter the results of a stored procedure using a where clause?

 

[moonshadow]

Ideally you could change the stored procedures to allow passing of filter criteria. However, you can insert the output from a stored procedure into a temporary table, and then run a select on that with a where clause. I think the syntax is
Insert #Temp
Exec sp_StoredProcedure

 

[SQLBill]

Something else you can do is re-write the stored procedure. BUT!!! give it a different name - do NOT overwrite the original stored procedure.

For example: I like sp_spaceused, but I only wanted certain information back. I wanted the database_size and data information. So, I copied the sp_spaceused script and saved it as my_sp_spaceused and made the appropriate changes. The original sp_spaceused still exists (good thing!) and I run my stored procedure (my_sp_spaceused) as a job on a daily basis.

-SQLBill

 

[sfriedman451]

Is it true that If I use the Insert #temp, I have to have already created the schema for it based upon the SPROC. Is there a quicker way to do this without having to manaully figure out the number, length and data types for the columns returned in the SPROC?

 

[moonshadow]

Sorry for the delay - been busy.
sfriedman451 - yes, I believe you'll have to script the creation of #temp. But help is at hand. You can use DTS to 'import' the results of your stored procedure to a table. The import wizard will create a table for you. You can then just script the table, cut & paste etc. and voila you can get a script to create your temporary table. However, I have a feeling the DTS wizard may produce the table by examining the data from stored procedure, and working out a best fit. Maybe someone else can confirm ?