SQL If Statements

[Silver Elf]

I am having a hard time understanding SQL if statements.

I have a good understanding of IF statements in other programming languages,
but I keep getting errors with SQL Server 7.

I am trying to use it within stored procedures.

Here are 2 examples I am trying, but it errors.

EXAMPLE 1:

CREATE PROCEDURE sp_Test

@filter int
as SELECT *
FROM tblTable

where ID = if @filter < 0

EXAMPLE 2:

CREATE PROCEDURE sp_Test

@filter int

if @filter != 0
as SELECT *
FROM tblTable

where ID = @filter

else
as SELECT *
FROM tblTable

where ID = 1

Can anyone shine some light on IF Statements within SQL Server 7?

Small code examples, etc.

Thanks in advance!

[Atrax _]

they look OK at first glance - but what errors are you getting??

and have you checked good ol' books online?

Atrax - MVP [Microsoft Most Valued Professional]

http://www.infinitemonkeys.ws/

Host with me for $100 a year!
http://www.atrax.ws/hosting/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[BP Margolin]

Silver,

The basic tenet is that a SQL command cannot contain embedded IF's. In
T-SQL, IF is a control of flow statement. So it controls the flow of
operations within a batch, but a SQL statement is a single command so it
makes no sense to embed an IF inside any SQL command.

Both ANSI SQL and T-SQL do however support the CASE expression . Note that
CASE is an expression, not a control of flow statement. In other words, by
using CASE one can return different values (of the same data type) depending
upon conditions encountered while processing the data.

Trying to explain the syntax of a language within a newsgroup is an
extremely task. Perhaps if you post a (simplified) table schema (CREATE
TABLE) and some sample data (INSERTs), and what you want the results to be
using the sample data, someone will post code that will help you understand
the SQL language better.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Silver Elf" <silve...@hotmail.com> wrote in message
news:10249581...@gaspra.spiretech.com...

[BP Margolin]

Atrax,

> they look OK at first glance

Glance again :-) Both contain simple syntax errors.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Atrax _" <anon...@devdex.com> wrote in message
news:3d17c079$0$179$7586...@news.frii.net...

[Dick Christoph]

Hi

IF in T-SQL is similar to If in other languages

Here is an example

----------------------------------

CREATE PROCEDURE sp_Test
@filter int
as

if @filter <> 0 -- Note "!=" is not valid T-SQL use <> for
"not equal to"

SELECT * FROM tblTable where ID = @filter
else

SELECT * FROM tblTable where ID = 1

--------------------------
To put multiple staements in the IF or ELSE section use Begin/End as in

------------------------------

CREATE PROCEDURE sp_Test
@filter int
as

Declare @Counter int

if @filter <> 0
begin
set @Counter = count(*) FROM tblTable where ID = @filter
SELECT @Counter, * FROM tblTable where ID = @filter
end
else
begin
set @Counter = count(*) FROM tblTable where ID = 1
SELECT @Counter, * FROM tblTable where ID = 1
end
------------------------------

To use "IF" logic within a Select statement you would use "CASE" statements.

HTH

-Dick Christoph

"Silver Elf" <silve...@hotmail.com> wrote in message
news:10249581...@gaspra.spiretech.com...