Prev: inner join on itself
Next: Need help with this query
From: M.K on 7 Apr 2010 01:15
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: Uri Dimant on 7 Apr 2010 04:15
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: Plamen Ratchev on 7 Apr 2010 10:21
See Erland's article on dynamic search conditions:
http://www.sommarskog.se/dyn-search.html

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 7 Apr 2010 12:13
CREATE PROCEDURE Test_something
(@in_something_type CHAR(3), @in_something_year INTEGER,
@in_something_level INTEGER = 0)
AS
SELECT *
FROM ABC
WHERE something_type = @in_something_type
AND something_year = @in_something_year
AND something_level
= CASE WHEN @in_something_level = 0
THEN something_level
ELSE @in_something_level END;

Or to be fancy :

COALESCE (NULLIF (@in_something_level, 0), something_level)
From: Uri Dimant on 8 Apr 2010 01:21
--CELKO-
Using COALESCE will prevent from the optimizer using an index ....



"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:79d42d0d-6f2e-42f9-8d08-aac15561a944(a)u34g2000yqu.googlegroups.com...
> CREATE PROCEDURE Test_something
> (@in_something_type CHAR(3), @in_something_year INTEGER,
> @in_something_level INTEGER = 0)
> AS
> SELECT *
> FROM ABC
> WHERE something_type = @in_something_type
> AND something_year = @in_something_year
> AND something_level
> = CASE WHEN @in_something_level = 0
> THEN something_level
> ELSE @in_something_level END;
>
> Or to be fancy :
>
> COALESCE (NULLIF (@in_something_level, 0), something_level)


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