Problem Using IF ELSE Statement

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

General SQL Server Forums

New to SQL Server Programming

Problem Using IF ELSE Statement

Author

Topic

seanmt
Starting Member

7 Posts

Posted - 2010-05-12 : 09:39:41

I am trying to run this piece of code in SQL Server 2008 but I am receiving an error and don't know what is wrong with it.

SELECT DISTINCT
CAST(c.LibraryNumber AS decimal(7,2)) AS LibraryNum,
CAST(c.RecordNumber AS varchar(255)) AS RecordNum,
CAST(0 AS decimal(5,2)) AS PurchaseCost,
CAST(c.NoCDs AS smallint) AS NoCds
IF RIGHT(c.Detail1,1) = '/'
BEGIN
	CAST(c.Detail1 + '/' + c.Detail2 AS nvarchar(600)) AS Detail
END
ELSE
BEGIN
	CAST(c.Detail1 + ' ' + c.Detail2 AS nvarchar(600)) AS Detail
END
FROM 
[CD Catalogue] c

The Error:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'CAST'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'CAST'.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 09:43:45

There is a comma missing before your IF.
Also I would do that using CASE instead of IF because it is easier to get it to work and easier to read (at least for me)

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

seanmt
Starting Member

7 Posts

Posted - 2010-05-12 : 09:48:33

I added the comma after NoCds but now it gives out this error..

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'CAST'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'CAST'.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-12 : 09:50:57

you can't use IF inside a SELECT statement. If is control of flow. You want to use CASE (as Webfred mentioned)

Try this:


SELECT DISTINCT
	CAST(c.LibraryNumber AS decimal(7,2)) AS LibraryNum
	, CAST(c.RecordNumber AS varchar(255)) AS RecordNum
	, CAST(0 AS decimal(5,2)) AS PurchaseCost
	, CAST(c.NoCDs AS smallint) AS NoCds
	, CASE RIGHT(c.Detail1,1)
		WHEN '/' THEN CAST(c.Detail1 + '/' + c.Detail2 AS nvarchar(600))
		ELSE CAST(c.Detail1 + ' ' + c.Detail2 AS nvarchar(600))
	END AS Detail
FROM 
	[CD Catalogue] c

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 09:52:20

Assuming Detail2 isn't a numeric type


SELECT DISTINCT
CAST(c.LibraryNumber AS decimal(7,2)) AS LibraryNum,
CAST(c.RecordNumber AS varchar(255)) AS RecordNum,
CAST(0 AS decimal(5,2)) AS PurchaseCost,
CAST(c.NoCDs AS smallint) AS NoCds,
CASE
WHEN RIGHT(c.Detail1,1) = '/'
	THEN CAST(c.Detail1 + '/' + c.Detail2 AS nvarchar(600))
	ELSE CAST(c.Detail1 + ' ' + c.Detail2 AS nvarchar(600))
END as Detail
FROM 
[CD Catalogue] c

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 09:53:08

The old rocker was too late again.