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.
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;
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.
1 Like
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
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.