Error 444 in UDF

Has anyone seen this error before?
(Am attempting to make my first UDF so is probably very basic.)
Error 444: Select statements within a function cannot return data to a client
This is the code:
CREATE FUNCTION udfActiveCaseSum
/******************************************************************************
** Eagle User Defined Function
** View Name: udfActiveCaseSum
** Description:
** Author:
** Date:
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -- -- ---
**
*******************************************************************************/
(
@.Other INT,
@.asofdate datetime,
@.CourtId int,
@.PersonID int
)
RETURNS INT
AS
BEGIN
SELECT distinct
ViewOrgPerson.PersonId,
ViewOrgPerson.FirstName,
ViewOrgPerson.LastName,
--ViewOrgPerson.RoleShort,
CASE TopRole.RoleCodeId WHEN 9 THEN 'MGMT' WHEN 8 THEN 'STAFF' ELSE 'XERROR' END as RoleShort,
Petition.CourtId,
Court.NameShort,
SUM(CASE WHEN Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('N') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NA') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('N') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NA') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('EP') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('EP') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('DV') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('ES') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('MD') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NV') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('TP') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VA') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VD') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VS') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('XS') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('PH') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('DV') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('ES') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('MD') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NV') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('TP') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VA') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VD') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VS') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('XS') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('PH') AND
Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('B') AND
Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('SR') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('SR') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('SR') AND
Petition.DocketPrefix <> 'V' AND PetitionCode.PetitionShort NOT IN ('V') AND
Petition.DocketPrefix <> 'S' AND PetitionCode.PetitionShort NOT IN ('<>S') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('D') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('DE') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('E') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('DE') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('EP') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('EP') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('MD') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TP') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TR') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VA') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VC') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VP') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('XP') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('PH') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('MD') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TP') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TR') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VA') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VC') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VP') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('XP') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('PH') AND
Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('K') AND
Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('KR') AND
Petition.DocketPrefix <> 'L' AND PetitionCode.PetitionShort NOT IN ('L')
THEN 1 ELSE 0 END) AS Other
FROM ViewOrgPerson with (noexpand)
INNER JOIN
Agent ON
ViewOrgPerson.OrgId = Agent.OrgId AND
ViewOrgPerson.PersonId = Agent.PersonId AND
-- ViewOrgPerson.RoleCodeId = Agent.RoleCodeId and
ViewOrgPerson.RoleShort IN ( 'LGS', 'LG') AND
ViewOrgPerson.CourtId = @.CourtId
inner join(
Select max (ViewOrgPerson.RoleCodeId) RoleCodeId , ViewOrgPerson.PersonId from
ViewOrgPerson WITH (NOEXPAND)
group by ViewOrgPerson.PersonId
)TopRole
on
TopRole.PersonId = Agent.PersonId
INNER JOIN
Petition ON Agent.PetitionId = Petition.PetitionId
INNER JOIN
Court ON Petition.CourtId = Court.CourtId and
Petition.CourtId = @.courtID
INNER JOIN
PetitionCode ON Petition.PetitionCodeId = PetitionCode.PetitionCodeId INNER JOIN
Appearance ON Petition.PetitionId = Appearance.PetitionId
and Appearance.EVENTDate >@.asofdate
--GROUP BY ViewOrgPerson.PersonId,
WHERE ViewOrgPerson.PersonId = @.PersonID
/**
ViewOrgPerson.FirstName,
ViewOrgPerson.LastName,
ViewOrgPerson.RoleShort,
TopRole.RoleCodeId,
Petition.CourtId,
Court.NameShort
**/
RETURN @.Other
ENDAndrew,
You have a SELECT statement which returns a resultset inside a scalar
function, can't have that. It would be like each invocation of SUBSTRING()
would return a table.
What type of function do you want to write? A scalar or a table function?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:8D4D2A43-0490-40CA-901B-B42DFEEA3BA8@.microsoft.com...
> Has anyone seen this error before?
> (Am attempting to make my first UDF so is probably very basic.)
> Error 444: Select statements within a function cannot return data to a
client
> This is the code:
> CREATE FUNCTION udfActiveCaseSum
>
/***************************************************************************
***
> ** Eagle User Defined Function
> ** View Name: udfActiveCaseSum
> ** Description:
> ** Author:
> ** Date:
>
****************************************************************************
***
> ** Change History
>
****************************************************************************
***
> ** Date: Author: Description:
> ** -- -- ---
> **
>
****************************************************************************
***/
> (
> @.Other INT,
> @.asofdate datetime,
> @.CourtId int,
> @.PersonID int
> )
> RETURNS INT
> AS
> BEGIN
> SELECT distinct
> ViewOrgPerson.PersonId,
> ViewOrgPerson.FirstName,
> ViewOrgPerson.LastName,
> --ViewOrgPerson.RoleShort,
> CASE TopRole.RoleCodeId WHEN 9 THEN 'MGMT' WHEN 8 THEN 'STAFF' ELSE
'XERROR' END as RoleShort,
> Petition.CourtId,
> Court.NameShort,
>
> SUM(CASE WHEN Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort
NOT IN ('N') AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NA')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('N')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NA')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('DV')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('ES')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NV')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VD')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VS')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('XS')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('DV')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('ES')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NV')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VD')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VS')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('XS')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('B')
AND
> Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'V' AND PetitionCode.PetitionShort NOT IN ('V')
AND
> Petition.DocketPrefix <> 'S' AND PetitionCode.PetitionShort NOT IN ('<>S')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('D')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('DE')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('E')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('DE')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TR')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VC')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('XP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TR')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VC')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('XP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('K')
AND
> Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('KR')
AND
> Petition.DocketPrefix <> 'L' AND PetitionCode.PetitionShort NOT IN ('L')
>
> THEN 1 ELSE 0 END) AS Other
> FROM ViewOrgPerson with (noexpand)
> INNER JOIN
> Agent ON
> ViewOrgPerson.OrgId = Agent.OrgId AND
> ViewOrgPerson.PersonId = Agent.PersonId AND
> -- ViewOrgPerson.RoleCodeId = Agent.RoleCodeId and
> ViewOrgPerson.RoleShort IN ( 'LGS', 'LG') AND
> ViewOrgPerson.CourtId = @.CourtId
> inner join(
> Select max (ViewOrgPerson.RoleCodeId) RoleCodeId ,
ViewOrgPerson.PersonId from
> ViewOrgPerson WITH (NOEXPAND)
> group by ViewOrgPerson.PersonId
> )TopRole
> on
> TopRole.PersonId = Agent.PersonId
> INNER JOIN
> Petition ON Agent.PetitionId = Petition.PetitionId
> INNER JOIN
> Court ON Petition.CourtId = Court.CourtId and
> Petition.CourtId = @.courtID
> INNER JOIN
> PetitionCode ON Petition.PetitionCodeId =PetitionCode.PetitionCodeId INNER JOIN
> Appearance ON Petition.PetitionId =Appearance.PetitionId
> and Appearance.EVENTDate >@.asofdate
> --GROUP BY ViewOrgPerson.PersonId,
> WHERE ViewOrgPerson.PersonId = @.PersonID
> /**
> ViewOrgPerson.FirstName,
> ViewOrgPerson.LastName,
> ViewOrgPerson.RoleShort,
> TopRole.RoleCodeId,
> Petition.CourtId,
> Court.NameShort
> **/
> RETURN @.Other
> END
>
>
>|||Thanks for the reply! Am not clear on the difference between the two - Basically have a large query that produces a report. Need to add functionality to catch all not listed in an "Other" column - Am trying to use a UDF to keep all the "<> this" and "<> that" code out of the calling proc.|||Change your udf to a table valued function ie..
create func myfun (parameters)
returns table
as
return( select.... )
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:8D4D2A43-0490-40CA-901B-B42DFEEA3BA8@.microsoft.com...
> Has anyone seen this error before?
> (Am attempting to make my first UDF so is probably very basic.)
> Error 444: Select statements within a function cannot return data to a
client
> This is the code:
> CREATE FUNCTION udfActiveCaseSum
>
/***************************************************************************
***
> ** Eagle User Defined Function
> ** View Name: udfActiveCaseSum
> ** Description:
> ** Author:
> ** Date:
>
****************************************************************************
***
> ** Change History
>
****************************************************************************
***
> ** Date: Author: Description:
> ** -- -- ---
> **
>
****************************************************************************
***/
> (
> @.Other INT,
> @.asofdate datetime,
> @.CourtId int,
> @.PersonID int
> )
> RETURNS INT
> AS
> BEGIN
> SELECT distinct
> ViewOrgPerson.PersonId,
> ViewOrgPerson.FirstName,
> ViewOrgPerson.LastName,
> --ViewOrgPerson.RoleShort,
> CASE TopRole.RoleCodeId WHEN 9 THEN 'MGMT' WHEN 8 THEN 'STAFF' ELSE
'XERROR' END as RoleShort,
> Petition.CourtId,
> Court.NameShort,
>
> SUM(CASE WHEN Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort
NOT IN ('N') AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NA')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('N')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NA')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('DV')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('ES')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NV')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VD')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VS')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('XS')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('DV')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('ES')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NV')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VD')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VS')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('XS')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('B')
AND
> Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'V' AND PetitionCode.PetitionShort NOT IN ('V')
AND
> Petition.DocketPrefix <> 'S' AND PetitionCode.PetitionShort NOT IN ('<>S')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('D')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('DE')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('E')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('DE')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TR')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VC')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('XP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TR')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VC')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('XP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('K')
AND
> Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('KR')
AND
> Petition.DocketPrefix <> 'L' AND PetitionCode.PetitionShort NOT IN ('L')
>
> THEN 1 ELSE 0 END) AS Other
> FROM ViewOrgPerson with (noexpand)
> INNER JOIN
> Agent ON
> ViewOrgPerson.OrgId = Agent.OrgId AND
> ViewOrgPerson.PersonId = Agent.PersonId AND
> -- ViewOrgPerson.RoleCodeId = Agent.RoleCodeId and
> ViewOrgPerson.RoleShort IN ( 'LGS', 'LG') AND
> ViewOrgPerson.CourtId = @.CourtId
> inner join(
> Select max (ViewOrgPerson.RoleCodeId) RoleCodeId ,
ViewOrgPerson.PersonId from
> ViewOrgPerson WITH (NOEXPAND)
> group by ViewOrgPerson.PersonId
> )TopRole
> on
> TopRole.PersonId = Agent.PersonId
> INNER JOIN
> Petition ON Agent.PetitionId = Petition.PetitionId
> INNER JOIN
> Court ON Petition.CourtId = Court.CourtId and
> Petition.CourtId = @.courtID
> INNER JOIN
> PetitionCode ON Petition.PetitionCodeId =PetitionCode.PetitionCodeId INNER JOIN
> Appearance ON Petition.PetitionId =Appearance.PetitionId
> and Appearance.EVENTDate >@.asofdate
> --GROUP BY ViewOrgPerson.PersonId,
> WHERE ViewOrgPerson.PersonId = @.PersonID
> /**
> ViewOrgPerson.FirstName,
> ViewOrgPerson.LastName,
> ViewOrgPerson.RoleShort,
> TopRole.RoleCodeId,
> Petition.CourtId,
> Court.NameShort
> **/
> RETURN @.Other
> END
>
>
>|||Is you want to return a table so you can use in a FROM clause of a SELECT
statement, then you want a table valued function. Check out Wayne's post.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:88954E4A-74AF-47AE-8F5C-D30CDD04B627@.microsoft.com...
> Thanks for the reply! Am not clear on the difference between the two -
Basically have a large query that produces a report. Need to add
functionality to catch all not listed in an "Other" column - Am trying to
use a UDF to keep all the "<> this" and "<> that" code out of the calling
proc.