SQLTeam.com | Weblogs | Forums

Call User Defined Function in an IF Statement


#1

Hi Guys,

I have a couple of UDFs that return Boolean values and I would like to call them in an IF statement in my stored procedure. I cannot seem to figure out the correct syntax to accomplish this, Would someone be so kind as to point me in the right direction. As an example I'm trying to do something like this IF (SELECT udf_udfname @Parm1,@Parm2) =1. Any assistance you can provide is appreciated.


#2

It depends on what type of function you've created...
If it's a scalar udf then you're on the right track...

IF (SELECT dbo.ScalarUDF('SomeValue')) = 1
BEGIN 
	PRINT('true');
END;
ELSE
BEGIN
	PRINT('false');
END;

If it's a table valued function, the syntax changes a little. Something closer to this...

IF EXISTS (SELECT 1 FROM dbo.IsAllDigits('123r567') iad WHERE iad.IsAllDigits = 1)
BEGIN
	PRINT ('all digits');
END;
ELSE 
BEGIN
	PRINT('non numeric');
END;

#3

Jason,
Thanks for you're reply. It's a scalar function. You're examples are helpful. I'm still figuring out the differences in stored procedure logic and syntax as opposed to traditional programming languages. I appreciate the assistance.


#4

I don't know if is the source of the confusion but that's the syntax for executing a stored procedure:

EXECUTE MySProc @Parm1,@Parm2

For a scalar function you need

SELECT dbo.MyFunction(@Parm1, @Parm2) AS MyAliasName

#5

Kristen,

Thanks for your input. Jason's examples provided the clarification I needed, My sample was typed in haste and I omitted the parenthesis and comma. My driver SProc is almost complete. Just a few more things I need to figure out then I will be ready to test it. I appreciate all your assistance.


#6

Glad to help. :slight_smile: