Simple IF Statement in T-SQL - 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 2005 Forums

Transact-SQL (2005)

Simple IF Statement in T-SQL

Author

Topic

Firebrand
Starting Member

24 Posts

Posted - 2010-07-06 : 09:05:57

Hello everyone,

the section of SQL below is from part of a larger query that I'm working on. I'd like to be able to set @CourseVar based on the outcome of @CourseID, however the eror is telling there's a problem next to IF and @CourseVar. This can't be that difficult can it?

Any help would be appreciated.

Many thanks
----
@CourseID int,
@CountryID int,
@LanguageID int,
@ContentType int,
@CourseVar char(20)

IF @CourseID = 179
@CourseVar = 'NOT NULL'
ELSE
@CourseVar = @CourseID
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-06 : 09:09:20

if
begin
...
end
else
begin
...
end

No, you're never too old to Yak'n'Roll if you're too young to die.

Firebrand
Starting Member

24 Posts

Posted - 2010-07-06 : 09:18:30

quote:
Originally posted by webfred

if
begin
...
end
else
begin
...
end

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks for the reply Webfred, nice of you to help. Although I'm not having any luck. After you advice, this is what I'm doing:

IF @CourseID = 179
BEGIN
@CourseVar = 'NOT NULL'
END
ELSE
BEGIN
@CourseVar = @CourseID
END

It's telling me there's a syntax error near IF

Any ideas?

Thanks again

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-06 : 09:30:56

the little word 'set' is missing ie. SET @Var = 'value'

No, you're never too old to Yak'n'Roll if you're too young to die.

Firebrand
Starting Member

24 Posts

Posted - 2010-07-06 : 09:53:51

quote:
Originally posted by webfred

the little word 'set' is missing ie. SET @Var = 'value'

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks Webfred, that's great and works. just one more quick question if I may? What's the best way to pass NOT NULL into a query? Passing it as text doesn't seem to work (which makes sense). Thanks again!

IF @CourseID = 00
BEGIN
SET @ContentVar = NOT NULL
END
ELSE
BEGIN
SET @ContentVar = @CourseID
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-06 : 10:02:40

To answer this I should see what you are trying to do.

No, you're never too old to Yak'n'Roll if you're too young to die.

Firebrand
Starting Member

24 Posts

Posted - 2010-07-06 : 10:30:51

quote:
Originally posted by webfred

To answer this I should see what you are trying to do.

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks again for your help!

If we look at the following section of query, I'd like xxx to be either '= CourseID' or 'IS NULL' depending on the outcome of the IF statement before hand. I can see why what I've done below won't work, but what I want to be able to do is pass xxx as a section of the Query rather that a single variable. Obviously the way I've written it below passes 'NOT NULL' etc as text. Any ideas? Thanks again.

IF @CourseID = 179
BEGIN
SET xxx = 'IS NULL'
END
ELSE
BEGIN
SET xxx = '= @CourseID'
END
---
SELECT........WHERE
lo.CountryLanguageID = co.CountryID
AND lo.LanguageID = co.LanguageID
AND co.CourseID xxx
AND co.ContentTypeID = @ContentType

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-06 : 10:37:49

This?

...
and ((co.CourseID IS NULL and @xxx = 'IS NULL') or (co.CourseID = @CourseID and @xxx = '= @CourseID') )
...

No, you're never too old to Yak'n'Roll if you're too young to die.

Firebrand
Starting Member

24 Posts

Posted - 2010-07-06 : 10:53:02

So we'd get:

SELECT........WHERE
lo.CountryLanguageID = co.CountryID
AND lo.LanguageID = co.LanguageID
AND co.CourseID IS NULL
AND co.ContentTypeID = @ContentType

(or)

SELECT........WHERE
lo.CountryLanguageID = co.CountryID
AND lo.LanguageID = co.LanguageID
AND co.CourseID = @CourseID
AND co.ContentTypeID = @ContentType

(Depending on the outcome of the IF statement)

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-07-06 : 11:13:13

Why bring strings into it? Does this do the job without any IF statement?


SELECT ....
FROM ....
WHERE ...

AND (
    co.[courseID] = @courseID
    AND @courseID <> 179

    OR (
        @courseID = 179
        AND co.[courseId] IS NULL
        )
    )

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-07-06 : 11:13:46

OFC -- this wouldn't work if @courseID IS NULL......

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Firebrand
Starting Member

24 Posts

Posted - 2010-07-06 : 11:19:39

Thanks everyone,

I'm not doing a very good job of explaining what I want, maybe I'll simplify things. Would the following be possible?

xxx = (AND co.CourseID = @CourseID)

SELECT * FROM tblCourses AS co
WHERE Country = 1
AND Language = 1
xxx
ORDER BY Country

Firebrand
Starting Member

24 Posts

Posted - 2010-07-06 : 12:15:28

Put it another way, when calling a procedure, I'd like to pass it an integer or NOT NULL. Thanks again for all your help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-06 : 12:28:28

quote:
Originally posted by webfred

This?

...
and ((co.CourseID IS NULL and @xxx = 'IS NULL') or (co.CourseID = @CourseID and @xxx = '= @CourseID') )
...

No, you're never too old to Yak'n'Roll if you're too young to die.

Even if this doesn't look very beautyful - it should work.
What is the problem with this?

No, you're never too old to Yak'n'Roll if you're too young to die.

Firebrand
Starting Member

24 Posts

Posted - 2010-07-07 : 04:29:46

Thanks for your help on this everyone. I haven't explained myself very well and have moved away from my original question.

I'll re write the question and put it back on the forum.

Many thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources