Return a Field from a User Function ?

Hello,
I am using SQL Server 2000 and I am wondering if it possible to create a
user fonction that return a field so I can use the return of the function in
a WHERE .
My original query I someting like this:
'=======================================
===
SELECT * FROM Table1
WHERE
case @.Workgroup
WHEN 1 THEN Table1.RouteQuart1
WHEN 2 THEN Table1.RouteQuart2
WHEN 3 THEN Table1.RouteQuart3
END = @.NumRoute
'=======================================
====
I want to create a function to remplace the CASE. This function would return
a field. And My new query would be :
'=======================================
===
SELECT * FROM Table1
WHERE
MyNewUserFunction = @.NumRoute
'=======================================
====
Is there a way to do this ? I the query will be more optimized ' If not
possible how to make my original query the most efficient?
Regards,
Gilles LabelleGilles Labelle,
Write three sps and call them from the main one.
create procedure dbo.p1
@.RouteQuart1 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart1 = @.RouteQuart1
return @.@.error
go
create procedure dbo.p2
@.RouteQuart2 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart2 = @.RouteQuart2
return @.@.error
go
create procedure dbo.p3
@.RouteQuart3 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart3 = @.RouteQuart3
return @.@.error
go
create procedure dbo.p4
@.Workgroup int,
@.NumRoute int
as
set nocount on
declare @.rv int
declare @.error int
if @.Workgroup = 1
begin
exec @.rv = dbo.p1 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
if @.Workgroup = 2
begin
exec @.rv = dbo.p2 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
if @.Workgroup = 3
begin
exec @.rv = dbo.p3 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
-- handle when the value of @.Workgroup is not 1, 2,3
end
end
end
return @.error
go
AMB
"Gilles Labelle" wrote:

> Hello,
> I am using SQL Server 2000 and I am wondering if it possible to create a
> user fonction that return a field so I can use the return of the function
in
> a WHERE .
> My original query I someting like this:
> '=======================================
===
> SELECT * FROM Table1
> WHERE
> case @.Workgroup
> WHEN 1 THEN Table1.RouteQuart1
> WHEN 2 THEN Table1.RouteQuart2
> WHEN 3 THEN Table1.RouteQuart3
> END = @.NumRoute
> '=======================================
====
> I want to create a function to remplace the CASE. This function would retu
rn
> a field. And My new query would be :
> '=======================================
===
> SELECT * FROM Table1
> WHERE
> MyNewUserFunction = @.NumRoute
> '=======================================
====
>
> Is there a way to do this ? I the query will be more optimized ' If not
> possible how to make my original query the most efficient?
>
> Regards,
> Gilles Labelle
>
>
>