Select only some records or everything in a stored proc.

I've got a stored proc that create a temporary table from a select with a
join of multiple tables. Depending on parameters, I may want either all of
the records or just some of them. Currently what I do is create the table
with every row in it and then delete any records I don't want.

Is there a better way to do it? I'd ideally like to have a single SQL
statement so that if I change the query, I only need to do it once.

I've also thought about having a check something like:
if ( limit )
select * into #table1
from....
where x = @passedin
else
select * into #table1
from ....

The problem is SQL complains that the table already exists when you try and
create the procedure.

So anybody have any suggestions?

TIA - Jeff.

Your IF SELECT * / ELSE SELECT * is interpreted by the parser as two (2)
create statements for the same table name, which, of course, is not allowed.
(Yes, I understand that only one can be created in this case, but the parser
makes a single pass (so I am told) so that is what it sees.)

You can create the temp table first, then in the IF / ELSE blocks you can do
INSERT INTO / SELECT FROM, which will cause no problem with duplicate table
definitions.

It also seems like what you are after is essentially a dynamic search
condition. If so, the strategies outlined here may help you.
http://www.sommarskog.se/dyn-search-2005.html

RLF


"Mr.Magic" wrote in message

> I've got a stored proc that create a temporary table from a select with a
> join of multiple tables. Depending on parameters, I may want either all of
> the records or just some of them. Currently what I do is create the table
> with every row in it and then delete any records I don't want.
>
> Is there a better way to do it? I'd ideally like to have a single SQL
> statement so that if I change the query, I only need to do it once.
>
> I've also thought about having a check something like:
> if ( limit )
> select * into #table1
> from....
> where x = @passedin
> else
> select * into #table1
> from ....
>
> The problem is SQL complains that the table already exists when you try
> and create the procedure.
>
> So anybody have any suggestions?
>
> TIA - Jeff.
>
>
>
>