Incorrect syntax near the keyword 'if'.

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

Transact-SQL (2008)

Incorrect syntax near the keyword 'if'.

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-11-02 : 13:50:14

What am I doing wrong

SELECT dbo.SCI_LKP.SCI_Name, Description
, CONVERT(VARCHAR, dbo.OffenderConfinement.Confinement_Start_Date, 101) as Confinement_Start_Date, CONVERT(VARCHAR, dbo.OffenderConfinement.Confinement_End_Date, 101) as Confinement_End_Date,
dbo.OffenderConfinement.IsCurrent
FROM dbo.OffenderConfinement LEFT OUTER JOIN
dbo.SCI_LKP ON dbo.OffenderConfinement.SCI_LKP_ID = dbo.SCI_LKP.SCI_LKP_ID
WHERE (dbo.OffenderConfinement.OffenderID = 2436)

union all
if @@rowcount = 0

select '' as SCI_Name, '' as Description, '' as Confinement_Start_Date, '' as Confinement_End_Date, '' as IsCurrent

Dave
Helixpoint Web Development
http://www.helixpoint.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-11-02 : 13:55:57

You can't put the IF statement there. A UNION is processed as a single query, you won't get a @@ROWCOUNT from just part of it.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-02 : 13:56:20

It's a single statement - how can you test @@rowcount in the middle?
You can check the filter to seee if it gets rows or populate a temp table or use a cte.

;with cte as
(
SELECT dbo.SCI_LKP.SCI_Name, Description
, CONVERT(VARCHAR, dbo.OffenderConfinement.Confinement_Start_Date, 101) as Confinement_Start_Date, CONVERT(VARCHAR, dbo.OffenderConfinement.Confinement_End_Date, 101) as Confinement_End_Date,
dbo.OffenderConfinement.IsCurrent
FROM dbo.OffenderConfinement LEFT OUTER JOIN
dbo.SCI_LKP ON dbo.OffenderConfinement.SCI_LKP_ID = dbo.SCI_LKP.SCI_LKP_ID
WHERE (dbo.OffenderConfinement.OffenderID = 2436)
)
select * from cte
union all
select '' as SCI_Name, '' as Description, '' as Confinement_Start_Date, '' as Confinement_End_Date, '' as IsCurrent
where not exists (select * from cte)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-11-02 : 13:56:53

Actually I guess I don't need the Union

Dave
Helixpoint Web Development
http://www.helixpoint.com

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-02 : 13:58:29

Only if you want a single default row rather than an empty recordset

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-11-02 : 14:03:14

I tried this, but when I there are no records I get nothing

Begin
SELECT dbo.SCI_LKP.SCI_Name, Description
, CONVERT(VARCHAR, dbo.OffenderConfinement.Confinement_Start_Date, 101) as Confinement_Start_Date, CONVERT(VARCHAR, dbo.OffenderConfinement.Confinement_End_Date, 101) as Confinement_End_Date,
dbo.OffenderConfinement.IsCurrent
FROM dbo.OffenderConfinement LEFT OUTER JOIN
dbo.SCI_LKP ON dbo.OffenderConfinement.SCI_LKP_ID = dbo.SCI_LKP.SCI_LKP_ID
WHERE (dbo.OffenderConfinement.OffenderID = 2435)
end
if @@rowcount = 0
Begin

select 'Nothing Reported' as SCI_Name, '' as Description, '' as Confinement_Start_Date, '' as Confinement_End_Date, '' as IsCurrent
end

Dave
Helixpoint Web Development
http://www.helixpoint.com

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-02 : 14:10:32

You will get an empty recordset followed by a recordset with one row (if there is no data in the first one).
Have a look at the cte solution I provided if you want a default row if there is no data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources