Help with user defined function

I have the following function:
create function dbo.AttributesList (@.Customerno varchar(10),
@.sold_to_sales_grp varchar(3), @.attr varchar(2))
returns varchar(15)
as
begin
declare @.sOut varchar(15)
set @.sOut = ''
if @.attr = '1'
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + x.distr_channel
from product1_channels x
where x.cust_no = @.Customerno and x.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '2'
begin
-- attribute table 2
-- attribute table 2
select @.sOut = @.sOut + ', ' + y.distr_channel
from product2_channels y
where y.cust_no = @.Customerno and y.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '3'
begin
-- attribute table 3
-- attribute table 3
select @.sOut = @.sOut + ', ' + z.distr_channel
from product3_channels z
where z.cust_no = @.Customerno and z.sold_to_sales_grp =
@.sold_to_sales_grp
end
-- previous
if @.attr = '4'
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + x.distr_channel
from product1_channels_prev x
where x.cust_no = @.Customerno and x.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '5'
begin
-- attribute table 2
-- attribute table 2
select @.sOut = @.sOut + ', ' + y.distr_channel
from product2_channels_prev y
where y.cust_no = @.Customerno and y.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '6'
begin
-- attribute table 3
-- attribute table 3
select @.sOut = @.sOut + ', ' + z.distr_channel
from product3_channels_prev z
where z.cust_no = @.Customerno and z.sold_to_sales_grp =
@.sold_to_sales_grp
end
if len(@.sOut) > 2
set @.sOut = substring(@.sOut, 3, len(@.sOut) - 2)
return @.sOut
end
which you call with a customer number, a sales group, and an attribute.
The tables it queries are built in a job that gets run overnight.
The results of the query are supposed to string together a distribution
channel if there are more than 1 returned.
product1 could return CH
product2 could return CH, DL
product3 could return nothing
The function as it is works properly. I get the anticipated results
when I run a query that calls this user function.
However, I want to write an online app that will allow the user to
select a channel from a dropdown box, and the query will then return any
data with that distribution channel anywhere in the current or
previous year's product areas.
The problem is - I can't make it work all the time.
If I put in the where clause:
'DL' in (dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) and so
on for each of the 6 possibilities, it'll return ones with a
Distribution channel = DL but only ones which START with a DL. If DL is
the second one in the list, it doesn't return anything.
I have also tried this in the HAVING clause, to see if I can get it to
come out there but can't.
How can I take the distribution channel (in this case DL) and return
records where DL is in any of the 6 product groups?
Any ideas/suggestions appreciated. I'm stumped.
BCBlasting Cap (goober@.christian.net) writes:
> -- attribute table 1
> select @.sOut = @.sOut + ', ' + x.distr_channel
> from product1_channels x
> where x.cust_no = @.Customerno and
>...
> The function as it is works properly. I get the anticipated results
> when I run a query that calls this user function.
> However, I want to write an online app that will allow the user to
> select a channel from a dropdown box, and the query will then return any
> data with that distribution channel anywhere in the current or
> previous year's product areas.
> The problem is - I can't make it work all the time.
This is because the correct behaviour of this query is undefined. See
http://support.microsoft.com/default.aspx?scid=287515.
If you are on SQL 2000, you will need to run a cursor to get this
right.
If you are on SQL 2005, there is built-in syntax for this thanks to
the FOR XML construct, here demonstrated by a sample query that I
keep around:
select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||<<This is because the correct behaviour of this query is undefined. See
http://support.microsoft.com/default.aspx?scid=287515.
If you are on SQL 2000, you will need to run a cursor to get this
right. >>
I am using SQL 2000.
The place I'm running the report is just a straight select, with the
functions being called for 6 columns being returned.
select
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region,
'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
from sales_customer_list as a
where cust_no in ('R1411600','R6713155')
group by
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region
having
-- if they have bought things in any of the last 3 years
(len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')) > 0 )
order by
a.ship_to_sales_grp,
a.ship_to_sales_rep_cd,
cust_name
How would you work a cursor into that select?
DECLARE @.AuthorID char(11)
DECLARE c1 CURSOR FOR
SELECT au_id
FROM authors
OPEN c1
FETCH NEXT FROM c1
INTO @.AuthorID
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.AuthorID
FETCH NEXT FROM c1
INTO @.AuthorID
END
CLOSE c1
DEALLOCATE c1
Also - is there a more efficient way to do this?
Thanks,
BC

> Blasting Cap (goober@.christian.net) writes:
when I run a query that calls this user function.
select a channel from a dropdown box, and the query will then return any
data with that distribution channel anywhere in the current or
previous year's product areas.
> This is because the correct behaviour of this query is undefined. See
> http://support.microsoft.com/default.aspx?scid=287515.
> If you are on SQL 2000, you will need to run a cursor to get this
> right.
> If you are on SQL 2005, there is built-in syntax for this thanks to
> the FOR XML construct, here demonstrated by a sample query that I
keep around:
> select CustomerID,
> substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
> -- strip the last ',' from the list
> from
> Customers c cross apply
> (select convert(nvarchar(30), OrderID) + ',' as [text()]
> from Orders o
> where o.CustomerID = c.CustomerID
> order by o.OrderID
> for xml path('')) as Dummy(OrdIdList)
> go
>
>|||On Thu, 25 May 2006 16:33:30 -0400, Blasting Cap wrote:

>I have the following function:
(snip)
Erland is correct - the UDF depends on undocumented behaviour. Even
though it works today, it might break tomorrow.
But Erland apparently missed the question you asked near the end of your
post:

>If I put in the where clause:
>'DL' in (dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) and so
>on for each of the 6 possibilities, it'll return ones with a
>Distribution channel = DL but only ones which START with a DL. If DL is
>the second one in the list, it doesn't return anything.
And yet, I'll let Erland answer that, since he has authored an excellent
page about this problem, and lots of possible solutions:
http://www.sommarskog.se/arrays-in-sql.html
However, in your case there might be a better way. Instead of first
using undocumented techniques to get a comma-seperated list and then
using a second technique to split those to tables, why not rewrite the
AttributesList to a table-valued function?
Hugo Kornelis, SQL Server MVP|||Blasting Cap (goober@.christian.net) writes:
> The place I'm running the report is just a straight select, with the
> functions being called for 6 columns being returned.
>...
> How would you work a cursor into that select?
In your UDF.

> Also - is there a more efficient way to do this?
Yes. Two options:
1) Upgrade to SQL 2005.
2) Do it client-side.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> On Thu, 25 May 2006 16:33:30 -0400, Blasting Cap wrote:
> And yet, I'll let Erland answer that, since he has authored an excellent
> page about this problem, and lots of possible solutions:
> http://www.sommarskog.se/arrays-in-sql.html
> However, in your case there might be a better way. Instead of first
> using undocumented techniques to get a comma-seperated list and then
> using a second technique to split those to tables, why not rewrite the
> AttributesList to a table-valued function?
For the WHERE clause why not simply use an EXISTS against the underlying
table:
WHERE EXISTS (SELECT *
FROM product1_channels p
WHERE p.cust_no = a.cust_no
AND p.distr_channel = 'DL')
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Blasting Cap (goober@.christian.net) writes:
> select
> a.cust_no,
> a.cust_name,
> a.sold_to_sales_grp,
> a.ship_to_sales_grp,
> a.sold_to_sales_rep_cd,
> a.ship_to_sales_rep_cd,
> a.csr,
> a.credit_mgr,
> a.sales_region,
> 'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
> 'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
> 'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
> 'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
> 'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
> 'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
> from sales_customer_list as a
> where cust_no in ('R1411600','R6713155')
> group by
> a.cust_no,
> a.cust_name,
> a.sold_to_sales_grp,
> a.ship_to_sales_grp,
> a.sold_to_sales_rep_cd,
> a.ship_to_sales_rep_cd,
> a.csr,
> a.credit_mgr,
> a.sales_region
> having
> -- if they have bought things in any of the last 3 years
> (len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')) > 0 )
> order by
> a.ship_to_sales_grp,
> a.ship_to_sales_rep_cd,
> cust_name
It seems unnecessary to call the UDFs a second time in the HAVING clause.
I'm a little uncertain of the effect of the GROUP BY in this SELECT as
there is no aggregate functions, but assuming that it works the way you
want I retain them. However you could use a derived table:
SELECT cust_no, cust_name, ...
FROM (select
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region,
'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
from sales_customer_list as a
where cust_no in ('R1411600','R6713155')
group by
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region) AS x
WHERE len(Prod1_curr) > 0 OR
len(Prod2_curr) > 0 OR
len(Prod3_curr) > 0 OR
len(Prod1_prev) > 0 OR
len(Prod2_prev) > 0 OR
len(Prod3_prev) > 0
order by
a.ship_to_sales_grp,
a.ship_to_sales_rep_cd,
cust_name
A derived table is a logical temp table within the query, but not materialis
ed,
and SQL Server recast computation order for the best query plan.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx