Prev: Temp Table Context Problem In SP called by trigger?
Next: Browse - How to Remove Duplicate Items
From: Julie on 14 Jun 2010 21:54
I am trying to create a report that shows a company name, all the advisors in that company, then the company's stats (there should be one row for each company). Each of these come from a different table. The advisors are currently being listed as one per line, which means the company name and the stats are all being repeated unneccesarily.

I want to concatinate the advisors, so that all the ones that belong to the same company show in the same cell.

I wrote this UDF which is located right before my SP:

CREATE FUNCTION [dbo].[ConcatAdvisorNames](@Company as varchar(20))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Company = CompanyName, @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), varchar1 + varchar2)
FROM tblProposal, tblGendata
WHERE tblProposal.ProposalID = tblGendata.ProposalID

RETURN @Output
END
GO

I call it inside the stored procedure with:

WHEN
tblGendata.varchar1 IN (select dbo.ConcatAdvisorNames(tblProposal.CompanyName))

where tblGendata.varchar1 appears in a previous select statement. Please help.

From http://www.developmentnow.com/g/113_2006_10_0_26_0/sql-server-programming.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/
From: Uri Dimant on 15 Jun 2010 01:19
Julie
Are you using SQL Server 2005 or onwards?


"Julie" <nospam(a)developmentnow.com> wrote in message
news:af5ba7cf-db49-4672-af33-8451a11a6985(a)developmentnow.com...
>I am trying to create a report that shows a company name, all the advisors
>in that company, then the company's stats (there should be one row for each
>company). Each of these come from a different table. The advisors are
>currently being listed as one per line, which means the company name and
>the stats are all being repeated unneccesarily.
>
> I want to concatinate the advisors, so that all the ones that belong to
> the same company show in the same cell.
>
> I wrote this UDF which is located right before my SP:
>
> CREATE FUNCTION [dbo].[ConcatAdvisorNames](@Company as varchar(20))
> RETURNS VARCHAR(8000)
> AS
> BEGIN
> DECLARE @Output VARCHAR(8000)
> SELECT @Company = CompanyName, @Output = COALESCE(@Output+', ', '') +
> CONVERT(varchar(20), varchar1 + varchar2)
> FROM tblProposal, tblGendata
> WHERE tblProposal.ProposalID = tblGendata.ProposalID
>
> RETURN @Output
> END
> GO
>
> I call it inside the stored procedure with:
>
> WHEN
> tblGendata.varchar1 IN (select
> dbo.ConcatAdvisorNames(tblProposal.CompanyName))
>
> where tblGendata.varchar1 appears in a previous select statement. Please
> help.
>
> From
> http://www.developmentnow.com/g/113_2006_10_0_26_0/sql-server-programming.htm
>
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com/g/


From: Erland Sommarskog on 15 Jun 2010 08:49
Julie (nospam(a)developmentnow.com) writes:
> I am trying to create a report that shows a company name, all the
> advisors in that company, then the company's stats (there should be one
> row for each company). Each of these come from a different table. The
> advisors are currently being listed as one per line, which means the
> company name and the stats are all being repeated unneccesarily.
>
> I want to concatinate the advisors, so that all the ones that belong to
> the same company show in the same cell.

Many would argue that this belongs in the presentation layer.

> I wrote this UDF which is located right before my SP:

Eh? Objects in an SQL Server database are not located before or after
each other. They are just stored without any order at all.

> CREATE FUNCTION [dbo].[ConcatAdvisorNames](@Company as varchar(20))
> RETURNS VARCHAR(8000)
> AS
> BEGIN
> DECLARE @Output VARCHAR(8000)
> SELECT @Company = CompanyName, @Output = COALESCE(@Output+', ', '') +
> CONVERT(varchar(20), varchar1 + varchar2)
> FROM tblProposal, tblGendata
> WHERE tblProposal.ProposalID = tblGendata.ProposalID

Beware that this is not guaranteed to work. The result of this statement
is undefined. You may get what you want, or you may get something else.
If you are on SQL 2005 or later, use you use FOR XML PATH, see Antih Sen's
article on http://www.projectdmx.com/tsql/rowconcatenate.aspx for details.

If you are on SQL 2000, there is even more reason to considering to do this
in the presentation layer.

Beside that, shouldn't your UDF have a WHERE condition?

> WHEN
> tblGendata.varchar1 IN (select
> dbo.ConcatAdvisorNames(tblProposal.CompanyName))

Sorry to be rude, but that is just insane. Syntactically you could write
this shorter:

tblGendata.varchar1 = dbo.ConcatAdvisorNames(tblProposal.CompanyName)

If you UDF returns a comma-separated list let's say A,B,C, then
varchar1 needs to have the value A,B,C for there to be a match.

I think what you want is

WHEN EXISTS (SELECT *
FROM tblProposal C
WHERE C.CompanyName = tblGendata.varchar1)


--
Erland Sommarskog, SQL Server MVP, esquel(a)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
 | 
Pages: 1
Prev: Temp Table Context Problem In SP called by trigger?
Next: Browse - How to Remove Duplicate Items