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.

Error 242 select with date conditions now failing

We have reinstalled SQL Server 2000 on a new server but select statements on
datetime fields are now failing where they didn't before. e.g.
SELECT * FROM CONTHEAD WHERE start_dt<='21/02/2005' and (expiry_dt is null
or expiry_dt>='21/02/2005')
gives the error
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
It works fine without the quotes around the dates but we have tons of code
that assumes quotes round dates is needed. There must be a global setting
somewhere that needs set. Does anyone know which setting it is?
Thanks for any help.
BernieBernie
What is a datatype of your start_dt and expiry_dt columns ?
What does it give you?
SELECT * FROM CONTHEAD WHERE start_dt<='20050221' and (expiry_dt is null
or expiry_dt>='20050221')
"Bernie Beattie" <BernieBeattie@.discussions.microsoft.com> wrote in message
news:066DA44E-AAAE-4134-9AEB-BC6C6E761A64@.microsoft.com...
> We have reinstalled SQL Server 2000 on a new server but select statements
on
> datetime fields are now failing where they didn't before. e.g.
> SELECT * FROM CONTHEAD WHERE start_dt<='21/02/2005' and (expiry_dt is null
> or expiry_dt>='21/02/2005')
> gives the error
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> It works fine without the quotes around the dates but we have tons of code
> that assumes quotes round dates is needed. There must be a global setting
> somewhere that needs set. Does anyone know which setting it is?
> Thanks for any help.
> Bernie|||This is why it pays to use the standard ISO format dates in code. Never rely
on implicit covnversions from regional date formats - the behaviour of those
conversions is too dependent on factors that you don't have complete control
over.
The following are the ISO standard forms for dates. These will work
regardless of server and connection settings:
'20050221' -- Just the date
'2005-02-21T17:59:00' -- Date/hours/minutes/seconds
'2005-02-21T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
The workaround for your situation is to set the default settings for
DATEFORMAT and LANGUAGE on your server. This will probably solve your
immediate problem but may not fix it permanently. The server settings are
only *default* settings so they can be overridden at the client. That's a
good thing because maybe not everyone will want the same settings but it
means that to make your code reliable you have to use the ISO format for dat
e
literals.
David Portas
SQL Server MVP
--|||Thanks for pointing me in the right direction David.
Our application that sends the select statements to SQL server can simply
send a set dateformat command when the ODBC connection is opened. That
should make all the application statements work just fine regardless of the
actual default setting on the server.
Bernie
"David Portas" wrote:

> This is why it pays to use the standard ISO format dates in code. Never re
ly
> on implicit covnversions from regional date formats - the behaviour of tho
se
> conversions is too dependent on factors that you don't have complete contr
ol
> over.
> The following are the ISO standard forms for dates. These will work
> regardless of server and connection settings:
> '20050221' -- Just the date
> '2005-02-21T17:59:00' -- Date/hours/minutes/seconds
> '2005-02-21T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
> The workaround for your situation is to set the default settings for
> DATEFORMAT and LANGUAGE on your server. This will probably solve your
> immediate problem but may not fix it permanently. The server settings are
> only *default* settings so they can be overridden at the client. That's a
> good thing because maybe not everyone will want the same settings but it
> means that to make your code reliable you have to use the ISO format for d
ate
> literals.
> --
> David Portas
> SQL Server MVP
> --