Prev: inner join on itself
Next: Need help with this query
From: M.K on 8 Apr 2010 03:14
Many thanks friends COALESCE worked perfect.

Dear Uri the sp I send is just an example where IF ELSE can help but my
actual SP is having many many conditional parameters. In your advised case I
have to repeat my long select statement everythime

"Uri Dimant" <urid(a)iscar.co.il> wrote in message
news:uN5FLpi1KHA.3648(a)TK2MSFTNGP05.phx.gbl...
> MK
> Why string?
> CREATE PROCEDURE [sp_TEST] @type char(3),@year int, @level_id int = 0
> AS
> IF @level_id int <>0
> select * from ABC where type = @type and year = @year AND
> level_id=(a)level_id
> ELSE
> select * from ABC where type = @type and year = @year
>
>
>
> "M.K" <mianksaeed(a)yahoo.com> wrote in message
> news:%235wboFh1KHA.752(a)TK2MSFTNGP04.phx.gbl...
>> Following Stored Procedure has three input parameters i.e @type, @year
>> and @level_id
>>
>> ##################
>> CREATE PROCEDURE [sp_TEST] @type char(3),@year int, @level_id int = 0
>> AS
>> select * from ABC where type = @type and year = @year
>> #################
>>
>> I want to write the select statement where if @level_id <> 0 passed to
>> procedure then a where clause for level_id should also be included. While
>> incase of @level_id = 0 no need to add anything in the select query
>> above.
>> One option if to build SQL as string using IF statement and then execute.
>> I want to avoid that approach.
>>
>>
>
>
From: --CELKO-- on 8 Apr 2010 11:51
>> Using COALESCE will prevent from the optimizer using an index .. <<

Assuming that something_level is an attribute with a relatively small
set of values -- say, grade_level at a school-- the it is not indexed.
I am not sure what the key in this table. My guess is that the
something_year is part of it, tho.

Using proprietary IF-THEN-ELSE procedural statements prevents portable
code.
From: Uri Dimant on 11 Apr 2010 02:48
--CELKO-
Assuming??? and what it isn't

> Using proprietary IF-THEN-ELSE procedural statements prevents portable
> code.
How does it impact on application? Will application stop working?



"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:a98b4701-25e1-46f0-b2fe-972bcf83f85d(a)w17g2000yqj.googlegroups.com...
>>> Using COALESCE will prevent from the optimizer using an index .. <<
>
> Assuming that something_level is an attribute with a relatively small
> set of values -- say, grade_level at a school-- the it is not indexed.
> I am not sure what the key in this table. My guess is that the
> something_year is part of it, tho.
>
> Using proprietary IF-THEN-ELSE procedural statements prevents portable
> code.


First  |  Prev  | 
Pages: 1 2
Prev: inner join on itself
Next: Need help with this query