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 Labelle

Gilles 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

Show quoteHide quote

Top N worst groups SQL problem
Splitting field contents into several columns (newbie)
SQL Server 2000 Automatic Setup Script.
Which primary key
NEED HELP W. QUERY USING DISTINCT AND SUM AND CASE