Using IF in Stored Procedure - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Using IF in Stored Procedure

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2002-08-12 : 09:47:49

Hi
I want condition 3 to be included in the query only when value of var1 <>0 and condition4 to be included in the query only when the value of var2 <>0. Else I want the query to stop after condition2. Any help will be appreciated.

CREATE PROC pqr

@var1 varchar(10),
@var2 varchar(10)
AS

SELECT COUNT(*)
FROM XYZ
WHERE condition1
AND condition2
AND condition3
AND condition4

GO

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-12 : 10:05:45

CREATE PROC pqr

@var1 varchar(10),
@var2 varchar(10)
AS

SELECT COUNT(*)
FROM XYZ
WHERE condition1
AND condition2
AND (@var1 = 0 or (condition3 and @var1 <> 0))
AND (@var2 = 0 or (condition4 and @var2 <> 0))

GO

Jay White
{0}

Edited by - Page47 on 08/12/2002 11:12:25

sahu74
Posting Yak Master

100 Posts

Posted - 2002-08-12 : 10:49:55

Jay
Thanks for the reply.

But I need to stop the execution of the query after condition2 if var1 = 0. Similarly, I need to stop the execution of the query after condition3 if var2 = 0.

Hope I am more clear now.

PKS

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-08-12 : 11:14:40

quote:

But I need to stop the execution of the query after condition2 if var1 = 0. Similarly, I need to stop the execution of the query after condition3 if var2 = 0.

I appreciate your question, though you're not approaching it in the correct fashion.

SQL is not a procedural language like most application languages are; it is a declarative language. That essentially means that you don't tell SQL Server how to do something, rather you tell it what you want the outcome to look like and its internal algorithms determine the best way to get there.

From your post I'll infer that you're looking for short-circuit evaluation - your "stop evaluating the query" question. Because SQL is declarative you can't tell SQL Server how to process the where clause - rest assured though that the optimizer is smart enough to process it efficiently. Page's edited SQL will provide the result set you are looking for.

Jonathan Boott, MCDBA
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources