remove condition in where clause dynamically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

remove condition in where clause dynamically

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-12-23 : 04:03:34

Hi All,

there is a sample stored proc as shown


CREATE PROC eg1
(
    @COL1 int
)
AS 
BEGIN

	IF @COL1 is null
	begin
		select * from table1 
	end
	
	ELSE
	begin
		select * from table1 where col1 = @COL1
	end

END

Is it possible to do away with the if-else and just have one select statement which will
automatically take care whether @COL1 is null or not.

i am also using CTE in my code so fitting CTE in an if-else clause will be really messy... i really wonder
if can even fit that.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-23 : 04:09:24

Alter ur stored procedure as below

ALTER PROC eg1
(
@COL1 int = NULL
)
AS
SET NOCOUNT ON
BEGIN

select * from table1 where ( @COL1 IS NULL OR col1 = @COL1)

END
SET NOCOUNT OFF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources