Help with UDF Syntax

I am a somewhat newbie to UDFs so don't laugh too hard!
I have 2 tables:
Tbl_Processes (first table)
ID
Process_Name
Included_In_Test
Tbl_Sub_Processes (second table)
ID
Sub_Process_Name
Lookup_To_Tbl_Processes_For_Process (points to the ID field of the
first table)
Included_In_Test
Here is what I am trying to do as explained in plain (I hope) english:
If Included_In_Test in the Processes table is 0 then return 0
BUT, if Included_In_Test in the Processes table is 1 then we have to find
out if any of the Sub Processes are also included. If none are, then we are
testing at the Process level and we still return 1
BUT, if only some of the Sub Processes are included in the test, then we
return 1 only for those that are included, otherwise we return zero.
Here is the code I am trying, and when I do a syntax check, I get a message
saying "Incorrect syntax near the keyword 'to'." Since there is no
occurrence of the character string 'to' in the code, I am puzzled. I believ
e
it has to do with my "SELECT SUM(Case " construct. Can anyone help? Or, am
I missing something completely different and simpler?
CREATE FUNCTION [dbo]. [Is_Process_And_Or_Sub_Process_Included_
In_Test]
(
@.ProcessID int,
@.SubProcessID int
)
RETURNS int
AS
BEGIN
Declare @.Result int
if @.ProcessID is null or @.SubProcessID is null
set @.Result to 0
ELSE
if (SELECT Included_In_Test FROM dbo.Tbl_Processes WHERE ID = @.ProcessID)
= 0
SET @.Result = 0
ELSE
if (SELECT SUM(Case When dbo.Tbl_Sub_Processes.Include_In_Test = 1 then
1 else 0 end) AS NumberOfSubProcessesIncluded) = 0
SET @.Result = 1
else
if (SELECT Included_In_Test FROM dbo.Tbl_Sub_Processess WHERE ID =
@.SubProcessID) = 1
SET @.Result = 1
else
Set @.Result = 0
ENDHi Bill,
set @.Result = 0
HTH, Jens Suessmeyer.|||Hi Bill,
whenever you get an error you can double click on that error and it will (in
many cases) will take you to the line where the error is.
Or the second way to look for an error is to do a <ctrl>+f and to a search
on whatever error you are getting.
In this case it was a trival mistake.
set @.Result = 0 as suggested by Jen
"Bill Sturdevant" wrote:

> I am a somewhat newbie to UDFs so don't laugh too hard!
> I have 2 tables:
> Tbl_Processes (first table)
> ID
> Process_Name
> Included_In_Test
> Tbl_Sub_Processes (second table)
> ID
> Sub_Process_Name
> Lookup_To_Tbl_Processes_For_Process (points to the ID field of the
> first table)
> Included_In_Test
> Here is what I am trying to do as explained in plain (I hope) english:
> If Included_In_Test in the Processes table is 0 then return 0
> BUT, if Included_In_Test in the Processes table is 1 then we have to find
> out if any of the Sub Processes are also included. If none are, then we a
re
> testing at the Process level and we still return 1
> BUT, if only some of the Sub Processes are included in the test, then we
> return 1 only for those that are included, otherwise we return zero.
> Here is the code I am trying, and when I do a syntax check, I get a messag
e
> saying "Incorrect syntax near the keyword 'to'." Since there is no
> occurrence of the character string 'to' in the code, I am puzzled. I beli
eve
> it has to do with my "SELECT SUM(Case " construct. Can anyone help? Or,
am
> I missing something completely different and simpler?
> CREATE FUNCTION [dbo]. [Is_Process_And_Or_Sub_Process_Included_
In_Test]
> (
> @.ProcessID int,
> @.SubProcessID int
> )
> RETURNS int
> AS
> BEGIN
> Declare @.Result int
> if @.ProcessID is null or @.SubProcessID is null
> set @.Result to 0
> ELSE
> if (SELECT Included_In_Test FROM dbo.Tbl_Processes WHERE ID = @.ProcessID
)
> = 0
> SET @.Result = 0
> ELSE
> if (SELECT SUM(Case When dbo.Tbl_Sub_Processes.Include_In_Test = 1 the
n
> 1 else 0 end) AS NumberOfSubProcessesIncluded) = 0
> SET @.Result = 1
> else
> if (SELECT Included_In_Test FROM dbo.Tbl_Sub_Processess WHERE ID =
> @.SubProcessID) = 1
> SET @.Result = 1
> else
> Set @.Result = 0
> END