IF Statement Help - 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)

IF Statement Help

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 15:36:21

Hi
I am trying to produce an IF statement that returns a value if the item is true or false. It is calling a function. The syntax looks like:

DECLARE
@inDATE DAETIME
SET @inDate = '2008-05-11'

IF
dbo.udf_FormatDate(@inDATE) IN (SELECT dbo.udf_FormatDate(ID) FROM DatePeriods)

RETURN 0
ELSE
RETURN 1

However when I run the script I get the following error:
Msg 178, Level 15, State 1, Line 8
A RETURN statement with a return value cannot be used in this context.
Msg 178, Level 15, State 1, Line 10
A RETURN statement with a return value cannot be used in this context.

Does anyone know what I could do?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-20 : 15:43:45

Is udf_FormatDate a scalar function?

You'll need to use IF EXISTS (SELECT...) where ... will probably include a JOIN

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 15:50:35

Hi

Yes it is a scalar function. How would the syntax go then?

Thanking you in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-20 : 15:58:47

Try this. I didn't need a join after all.


DECLARE @inDATE datetime

SET @inDATE = '2008-05-11'

IF EXISTS (SELECT * FROM DatePeriods WHERE dbo.udf_FormatDate(ID) = dbo.udf_FormatDate(@inDATE))
	SELECT 0
ELSE
	SELECT 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 16:55:31

Hey, thats GREAT!!!

Thanks for all your help!!!

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 16:59:45

Just a quickie! Is the parts stating SELECT 0 and SELECT 1 the same as RETURN statement I was trying to use originally?

Thanks

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-21 : 04:46:10

Hi Guys

I've tried to put the IF statement into a function with the syntax:

CREATE FUNCTION CheckDateReturn
(@InDATE DATETIME)
RETURNS BIT

AS

BEGIN
DECLARE @Output BIT
SET @Output = IF EXISTS(SELECT * FROM DatePeriods WHERE dbo.udf_FormatDate(ID) = dbo.udf_FormatDate(@InDATE))
RETURN 0
ELSE
RETURN 1
RETURN @Output

But I keep on getting syntax errors:
Msg 156, Level 15, State 1, Procedure CheckDateReturn, Line 14
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Procedure CheckDateReturn, Line 18
Incorrect syntax near '@Output'.

I'm totally stuck on this as I haven't really used IF statements in functions before!

Any ideas?

Thanks

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-05-21 : 05:28:07

if exists(...)
return 0
else
return 1

no @output needed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-21 : 05:28:42

quote:
Originally posted by rcr69er

Hi Guys

I've tried to put the IF statement into a function with the syntax:

CREATE FUNCTION CheckDateReturn
(@InDATE DATETIME)
RETURNS BIT

AS

BEGIN
DECLARE @Output BIT
IF EXISTS(SELECT * FROM DatePeriods WHERE dbo.udf_FormatDate(ID) = dbo.udf_FormatDate(@InDATE))
SET @Output = 0
ELSE
SET @Output = 1
RETURN @Output
ENDGO
But I keep on getting syntax errors:
Msg 156, Level 15, State 1, Procedure CheckDateReturn, Line 14
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Procedure CheckDateReturn, Line 18
Incorrect syntax near '@Output'.

I'm totally stuck on this as I haven't really used IF statements in functions before!

Any ideas?

Thanks

Change like this

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-21 : 05:46:14

Hey thanks yet again!!!

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-21 : 08:44:06

Hi

Is there anyway of showing an error message if the return value is 1?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-21 : 11:27:05

quote:
Originally posted by rcr69er

Hi

Is there anyway of showing an error message if the return value is 1?

Thanks

Error message? like what?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-21 : 14:04:00

something like 'record will not been inserted'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 15:03:44

You can use RAISERROR.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-22 : 03:46:25

Hi

I'm trying to include it in the function using the following:

CREATE FUNCTION [dbo].[udf_CheckDateReturnERROR]
(@InDATE DATETIME)
RETURNS BIT

AS

BEGIN
DECLARE @Output BIT

IF EXISTS (SELECT ID FROM DatePeriods WHERE dbo.udf_FormatDate(ID) = dbo.udf_FormatDate(@InDATE))
SET @Output = 0
ELSE
SET @Output = 1
RAISERROR ('Cannot Insert into Table',10,1)
RETURN @Output
END

But I keep on getting the following error:
Msg 443, Level 16, State 14, Procedure udf_ECG_CheckDateReturnERROR, Line 19
Invalid use of side-effecting or time-dependent operator in 'RAISERROR' within a function.

Anyone know what I could do?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources