filter select

[bmeza]

I have @depto beachible and I want to make a filter in "select" when @depto is different from blanks

here its my code:
declare @depto as varchar(12)
set @depto='1500'
if @p_depto!='' begin
select distinct p_parte,p_depto from products where
p_type in ('A', 'P') and p_status='A' and p_depto=@p_depto
end
else
select distinct p_parte,p_depto from products where
p_type in ('A', 'P') and p_status='A'
end

I want to put the result in a "CURSOR" somebody knows how doing it? My principal problem its the filter how to filter the information

 

[gmmastros]

Try using this instead. It should effectively return the same data as your IF check query.

[COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color] p_parte,p_depto 
[COLOR=blue]from[/color]   products 
[COLOR=blue]where[/color]  p_type in ([COLOR=red]'A'[/color], [COLOR=red]'P'[/color]) 
       and p_status=[COLOR=red]'A'[/color]   
       and (@p_depto = [COLOR=red]''[/color] Or p_depto=@p_depto)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

From where @p_depto come from? I see only @depto declared here, but in case you have a typo:

[COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color] p_parte,
                p_depto
       [COLOR=blue]from[/color] products
       [COLOR=blue]where[/color] p_type in ([COLOR=red]'A'[/color], [COLOR=red]'P'[/color]) and 
             p_status=[COLOR=red]'A'[/color]  and 
             (@p_depto = [COLOR=red]''[/color] OR p_depto=@p_depto)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bmeza]

when the @p_depto its blanks i want to select all, wnen its different i just want to select that depto
how can do it?

 

[bborissov]

What do you mean with "ALL"?
Did you want ALL records no matter what p_type and p_status they have?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

And no one explained why you should avoid the use of a cursor unless no other solution exists. Cursors are extremely bad for performance. Queries using a cursor can take minutes or hours (or even days) to process when a set-based solution such as you were given above can take milleseconds, seconds or minutes (depending on the complexity). Depending on the size of the tables involved and the number of times a query will be run, you could bring the system to a halt by using a cursor-based solution. If I were you I would forget that I ever heard of a cursor and never ever consider using it. Some places I have worked forbid the use of a cursor unless the dba approves it and they did that for a good reason. Cursors are almost never needed for select, insert, delete or update statements. Only truly expert dba-type people generally really have a need to write one. If you are often writing cursors now, you need to stop or your system will be unable to handle the load as it grows large (and redoing a badly designed, poorly performing production system once it has millions of records is NOT a fun task).

Questions about posting. See faq183-874