multiple parameters for user defined function

10 Feb 2006 10:26 PM

AkAlan

I have a udf with a single parameter @Status which I use to return records
based on a status of Red, Amber, Green, or ALL(DEFAULT). It works great but I
want to be able to return records that are either Red or Amber. I have tried
entering values to the function when it prompts me a number of different ways
such as

Red OR Amber,
Red,Amber
'Red' Or 'Amber'
etc

There must be a correct syntax for achieving the dseired results but I'm
running out of options that seem logical. How is this done? Thanks.

10 Feb 2006 10:52 PM

Adam Machanic

You'd have to define two parameters.

--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

Show quote

"AkAlan" <AkA***@discussions.microsoft.com> wrote in message
news:80FBE123-99E6-4FAA-BE0E-C8A38AE1E382@microsoft.com...
>I have a udf with a single parameter @Status which I use to return records
> based on a status of Red, Amber, Green, or ALL(DEFAULT). It works great
> but I
> want to be able to return records that are either Red or Amber. I have
> tried
> entering values to the function when it prompts me a number of different
> ways
> such as
>
> Red OR Amber,
> Red,Amber
> 'Red' Or 'Amber'
> etc
>
> There must be a correct syntax for achieving the dseired results but I'm
> running out of options that seem logical. How is this done? Thanks.

10 Feb 2006 11:07 PM

AkAlan

i did, so now I get promted twice, I put red in the first promt Amber in the
second and still no records returned. It's not a data proble, there are both
red and amber records in the table.

Show quote

"Adam Machanic" wrote:

> You'd have to define two parameters.
>
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
> "AkAlan" <AkA***@discussions.microsoft.com> wrote in message
> news:80FBE123-99E6-4FAA-BE0E-C8A38AE1E382@microsoft.com...
> >I have a udf with a single parameter @Status which I use to return records
> > based on a status of Red, Amber, Green, or ALL(DEFAULT). It works great
> > but I
> > want to be able to return records that are either Red or Amber. I have
> > tried
> > entering values to the function when it prompts me a number of different
> > ways
> > such as
> >
> > Red OR Amber,
> > Red,Amber
> > 'Red' Or 'Amber'
> > etc
> >
> > There must be a correct syntax for achieving the dseired results but I'm
> > running out of options that seem logical. How is this done? Thanks.
>
>
>

10 Feb 2006 11:20 PM

Hugo Kornelis

On Fri, 10 Feb 2006 15:07:29 -0800, AkAlan wrote:

>i did, so now I get promted twice, I put red in the first promt Amber in the
>second and still no records returned. It's not a data proble, there are both
>red and amber records in the table.

Hi AkAlan,

The query should be like this:

.....
WHERE Colour = @Parameter1
OR Colour = @Parameter2

--
Hugo Kornelis, SQL Server MVP

10 Feb 2006 11:07 PM

Jens

By the way beside of defining two parameteres you could also (if there
are only three option -Red,Amber,Green) define three parameters which
would get around chopping the values out of the string, something like
a signature defining:

CREATE FUNCTION dbo.SomeFunction(@Green BIT,@Amber BIT,@Red BIT)

calling it that way: SELECT * FROM dbo.SomeFunction(1,1,1)

HTH, Jens Suessmeyer.

10 Feb 2006 11:15 PM

AkAlan

Figured it out. I put the second parameter in the or column. now I get
promted twice and the correct results are diplayed. Thanks for the inputs.

Show quote

"AkAlan" wrote:

> I have a udf with a single parameter @Status which I use to return records
> based on a status of Red, Amber, Green, or ALL(DEFAULT). It works great but I
> want to be able to return records that are either Red or Amber. I have tried
> entering values to the function when it prompts me a number of different ways
> such as
>
> Red OR Amber,
> Red,Amber
> 'Red' Or 'Amber'
> etc
>
> There must be a correct syntax for achieving the dseired results but I'm
> running out of options that seem logical. How is this done? Thanks.

10 Feb 2006 11:24 PM

Erland Sommarskog

AkAlan (AkA***@discussions.microsoft.com) writes:

> I have a udf with a single parameter @Status which I use to return
> records based on a status of Red, Amber, Green, or ALL(DEFAULT). It
> works great but I want to be able to return records that are either Red
> or Amber. I have tried entering values to the function when it prompts
> me a number of different ways such as
>
> Red OR Amber,
> Red,Amber
> 'Red' Or 'Amber'
> etc
>
> There must be a correct syntax for achieving the dseired results but I'm
> running out of options that seem logical. How is this done? Thanks.

You would need to rewrite your UDF to handle multiple parameters one
way or another.

Since you did not post the code to the UDF I cannot give any suggestion
on how to you should rewrite it.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx