[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

what wrong with this

Calvin Willman

3/26/2007 11:11:00 AM

ALTER FUNCTION [dbo].[fn_KPI_GET_EXCL_PRODUCTS]

(

@CustomerID int

)

RETURNS TABLE

AS RETURN (

IF (SELECT COUNT(ProductCode) from dbo.KPI_PROD_CDE_EXCL AS ProductCode
WHERE CustomerID = @CustomerID) > 0

BEGIN

SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
CustomerID = @CustomerID

END

ELSE

BEGIN

SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
CustomerID = @CustomerID

END

)
9 Answers

Uri Dimant

3/26/2007 11:27:00 AM

0

Calvin

My guess you need something like that

CREATE FUNCTION [dbo].[fn_KPI_GET_EXCL_PRODUCTS]
(

@CustomerID int
)

RETURNS TABLE
AS RETURN (
SELECT Customerid from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
CustomerID = @CustomerID AND ProductCode>0

)




"Calvin Willman" <anon@anon.com-no-spam> wrote in message
news:OxUhCe5bHHA.1240@TK2MSFTNGP04.phx.gbl...
> ALTER FUNCTION [dbo].[fn_KPI_GET_EXCL_PRODUCTS]
>
> (
>
> @CustomerID int
>
> )
>
> RETURNS TABLE
>
> AS RETURN (
>
> IF (SELECT COUNT(ProductCode) from dbo.KPI_PROD_CDE_EXCL AS ProductCode
> WHERE CustomerID = @CustomerID) > 0
>
> BEGIN
>
> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
> CustomerID = @CustomerID
>
> END
>
> ELSE
>
> BEGIN
>
> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
> CustomerID = @CustomerID
>
> END
>
> )
>
>


EMartinez

3/26/2007 11:32:00 AM

0

On Mar 26, 6:11 am, "Calvin Willman" <a...@anon.com-no-spam> wrote:
> ALTER FUNCTION [dbo].[fn_KPI_GET_EXCL_PRODUCTS]
>
> (
>
> @CustomerID int
>
> )
>
> RETURNS TABLE
>
> AS RETURN (
>
> IF (SELECT COUNT(ProductCode) from dbo.KPI_PROD_CDE_EXCL AS ProductCode
> WHERE CustomerID = @CustomerID) > 0
>
> BEGIN
>
> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
> CustomerID = @CustomerID
>
> END
>
> ELSE
>
> BEGIN
>
> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
> CustomerID = @CustomerID
>
> END
>
> )


If my memory serves me correctly, I don't believe that you can include
conditional statements in inline, table-valued functions. Look into
using a multi-statement function instead. Hope this helps.

Regards,

Enrique Martinez
Sr. Software Consultant

Calvin Willman

3/26/2007 2:01:00 PM

0

Thanks Enrique,

You're right, I was trying to cut corners I think here... but the
Multi-Statement Function is the one I'm after.



"EMartinez" <emartinez.pr1@gmail.com> wrote in message
news:1174908732.475129.109500@y80g2000hsf.googlegroups.com...
> On Mar 26, 6:11 am, "Calvin Willman" <a...@anon.com-no-spam> wrote:
>> ALTER FUNCTION [dbo].[fn_KPI_GET_EXCL_PRODUCTS]
>>
>> (
>>
>> @CustomerID int
>>
>> )
>>
>> RETURNS TABLE
>>
>> AS RETURN (
>>
>> IF (SELECT COUNT(ProductCode) from dbo.KPI_PROD_CDE_EXCL AS ProductCode
>> WHERE CustomerID = @CustomerID) > 0
>>
>> BEGIN
>>
>> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
>> CustomerID = @CustomerID
>>
>> END
>>
>> ELSE
>>
>> BEGIN
>>
>> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
>> CustomerID = @CustomerID
>>
>> END
>>
>> )
>
>
> If my memory serves me correctly, I don't believe that you can include
> conditional statements in inline, table-valued functions. Look into
> using a multi-statement function instead. Hope this helps.
>
> Regards,
>
> Enrique Martinez
> Sr. Software Consultant
>


Calvin Willman

3/26/2007 2:02:00 PM

0

Hi Uri,

I think I needed Multi-Statement Function...

Thanks for you reply.

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:%23VAfXn5bHHA.1296@TK2MSFTNGP02.phx.gbl...
> Calvin
>
> My guess you need something like that
>
> CREATE FUNCTION [dbo].[fn_KPI_GET_EXCL_PRODUCTS]
> (
>
> @CustomerID int
> )
>
> RETURNS TABLE
> AS RETURN (
> SELECT Customerid from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
> CustomerID = @CustomerID AND ProductCode>0
>
> )
>
>
>
>
> "Calvin Willman" <anon@anon.com-no-spam> wrote in message
> news:OxUhCe5bHHA.1240@TK2MSFTNGP04.phx.gbl...
>> ALTER FUNCTION [dbo].[fn_KPI_GET_EXCL_PRODUCTS]
>>
>> (
>>
>> @CustomerID int
>>
>> )
>>
>> RETURNS TABLE
>>
>> AS RETURN (
>>
>> IF (SELECT COUNT(ProductCode) from dbo.KPI_PROD_CDE_EXCL AS ProductCode
>> WHERE CustomerID = @CustomerID) > 0
>>
>> BEGIN
>>
>> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
>> CustomerID = @CustomerID
>>
>> END
>>
>> ELSE
>>
>> BEGIN
>>
>> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
>> CustomerID = @CustomerID
>>
>> END
>>
>> )
>>
>>
>
>


jeevan

3/26/2007 7:20:00 PM

0

>> On Mar 26, 6:11 am, "Calvin Willman" <a...@anon.com-no-spam> wrote:
>>> ALTER FUNCTION [dbo].[fn_KPI_GET_EXCL_PRODUCTS]
>>>
>>> (
>>>
>>> @CustomerID int
>>>
>>> )
>>>
>>> RETURNS TABLE
>>>
>>> AS RETURN (
>>>
>>> IF (SELECT COUNT(ProductCode) from dbo.KPI_PROD_CDE_EXCL AS ProductCode
>>> WHERE CustomerID = @CustomerID) > 0
>>>
>>> BEGIN
>>>
>>> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
>>> CustomerID = @CustomerID
>>>
>>> END
>>>
>>> ELSE
>>>
>>> BEGIN
>>>
>>> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
>>> CustomerID = @CustomerID
>>>
>>> END
>>>
>>> )

Interesting. You have an IF statement to check if the COUNT(ProductCode) is
> 0 where CustomerID = @CustomerID. If it is, you do the following:

SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
CustomerID = @CustomerID

If it isn't, you do the following:

SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
CustomerID = @CustomerID

What's the point of the IF statement in this situation?


EMartinez

3/27/2007 12:52:00 AM

0

On Mar 26, 9:01 am, "Calvin Willman" <a...@anon.com-no-spam> wrote:
> Thanks Enrique,
>
> You're right, I was trying to cut corners I think here... but the
> Multi-Statement Function is the one I'm after.
>
> "EMartinez" <emartinez....@gmail.com> wrote in message
>
> news:1174908732.475129.109500@y80g2000hsf.googlegroups.com...
>
> > On Mar 26, 6:11 am, "Calvin Willman" <a...@anon.com-no-spam> wrote:
> >> ALTER FUNCTION [dbo].[fn_KPI_GET_EXCL_PRODUCTS]
>
> >> (
>
> >> @CustomerID int
>
> >> )
>
> >> RETURNS TABLE
>
> >> AS RETURN (
>
> >> IF (SELECT COUNT(ProductCode) from dbo.KPI_PROD_CDE_EXCL AS ProductCode
> >> WHERE CustomerID = @CustomerID) > 0
>
> >> BEGIN
>
> >> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
> >> CustomerID = @CustomerID
>
> >> END
>
> >> ELSE
>
> >> BEGIN
>
> >> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
> >> CustomerID = @CustomerID
>
> >> END
>
> >> )
>
> > If my memory serves me correctly, I don't believe that you can include
> > conditional statements in inline, table-valued functions. Look into
> > using a multi-statement function instead. Hope this helps.
>
> > Regards,
>
> > Enrique Martinez
> > Sr. Software Consultant


You're welcome. Glad I could be of assistance.

Regards,

Enrique Martinez
Sr. Software Consultant

ML

3/28/2007 1:08:00 AM

0

"If they're there get'em, if they ain't there get'em just in case."

:)

ML

---
http://milambda.blo...

Alejandro Mesa

3/28/2007 1:59:00 AM

0

Calvin Willman,

What are you trying to achieve with the IF?

Both "select" statements are pulling the same.


AMB


"Calvin Willman" wrote:

> ALTER FUNCTION [dbo].[fn_KPI_GET_EXCL_PRODUCTS]
>
> (
>
> @CustomerID int
>
> )
>
> RETURNS TABLE
>
> AS RETURN (
>
> IF (SELECT COUNT(ProductCode) from dbo.KPI_PROD_CDE_EXCL AS ProductCode
> WHERE CustomerID = @CustomerID) > 0
>
> BEGIN
>
> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
> CustomerID = @CustomerID
>
> END
>
> ELSE
>
> BEGIN
>
> SELECT ProductCode from dbo.KPI_PROD_CDE_EXCL AS ProductCode WHERE
> CustomerID = @CustomerID
>
> END
>
> )
>
>
>

ML

3/28/2007 2:02:00 AM

0

You can't use multiple statements inside the RETURN statement.
Use the multi statement syntax for UDF's (look it up in Books Online) or
explain what you actually need (for example: why are you using the same
SELECT query for both cases?).


ML

---
http://milambda.blo...