Invalid object name when I try to create a scaler udf in a stored procedure.

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/
0
Julie
6/15/2010 1:54:33 AM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
1498 Views

Similar Articles

[PageSpeed] 30

Julie
Are   you using SQL Server 2005 or onwards?


"Julie" <nospam@developmentnow.com> wrote in message 
news:af5ba7cf-db49-4672-af33-8451a11a6985@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/ 


0
Uri
6/15/2010 5:19:11 AM
Julie (nospam@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@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
0
Erland
6/15/2010 12:49:02 PM
Reply:

Similar Artilces:

2nd Try Here: Quote to Sales Receipt
We are printing quotes to the full page, when picking up the quote for sale - we would like this receipt to print to the 40 Column. This is not happening. Can a quote that is converted to a final sale be printed as 40 col sales receipt? Additionally, when a quote is picked up and tendered as a final sale, if a CC is used as the tender option, there is no field for a signature on the full page, but it is noted to require signature under the denomination. I found one article related to a quote with multiple sales receipts, but I do not understand what it was saying - KB 874092. -- Joce...

Create an ASP.NET Web Service
Not sure if this is the correct group to be posting this at but here goes. I am in the MSDN Library and doing the step-by-step instructions on the "Walkthrough: Creating a Distributed Application" and I appear to be a lost at step 5 in the section titled "To create an ASP.NET Web Service project". Line 5 says "In Solution Explorer, double-click Service1.asmx to select it". I do not see Servicece1.asmx in the Solution Explorer after following all the previous steps to the letter. In fact I don't see anything with an asmx extension. The closest thi...

Creating a combination chart using cluster column & stacked bar t.
I need to create a combination chart, using cluster column and stacked bar types. I need to "cluster" two columns side by side without space in between, with stacked bars within each of these columns. Then, have space along the x axis, and have 2 more columns clustered made up of stacked bars within. I am charting 2 years side by side, with different types of data within the columns, so need to stack/segment the column. How do I create and combination chart, and how do I select these 2 types to combine together? HELP, PLEASE?!? If you stagger your data, you can create sid...

Create a calendar in excel for the next 4 yeara
How can I create a calendar in excel for the next 4 years? Begin date is October 31, 2005 and end date is October 31, 2010. -- dyukon ------------------------------------------------------------------------ dyukon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30062 View this thread: http://www.excelforum.com/showthread.php?threadid=497408 What form do you want the calendar to take? A very simple way is to enter your start date in cell A1 and format it as mmmm dd, yyyy. Then in cell A2, enter the formula: =A1 + 1 and ensure that this is formatted in the...

Hyperlink powerpoint object to excel range?
I'm using a powerpoint presentation to show several ranges on several worksheets in an excel workbook. No problem linking the excel file to powerpoint but is there a way to link powerpoint to a range address within that file? I am hoping to flip back and forth with several powerpoint links to particular worksheet ranges. Thanks Your advice will be appreciated ...

associating scripts to outlook objects
hi, i am new to outlook programming. i want to know whether it is possible to associate scripts(VBScript or JScript) to the Objects exposed by OutLook such that they are always active and i can perform custom actions in my add-in whenever something of my interest takes place or to perform some automation. thanks, gshetty ...

Forward misspelled names to user account if doman is correct.
Is it possible to catch for instance a misspelled name like moike@domain.com that should be mike@domain.com and forward it to another account? It should be able to pick up anything (like *@domain.com) and forward it to joe@domain.com. Thanks in advance. Mike Are you really sure that you want to do this? If so, you can take a look at the methods listed here. http://hellomate.typepad.com/exchange/2003/08/exchange_catcha.html -- Ben Winzenz Exchange MVP MessageOne "pakitloss" <pakitloss@discussions.microsoft.com> wrote in message news:24DB863D-49F5-457A-BA47-B7D26F422...

Named as rlbook1, re-open? How?
....during library internet sesion this occurred. I don't understand your question. Please put the entire question in the body of the message. It's rather cryptic now. "rlscreen001" <rlscreen001@discussions.microsoft.com> wrote in message news:577EA294-53D3-4D94-A042-7CB9C58B7F64@microsoft.com... > ...during library internet sesion this occurred. ...

User folder on desktop has a bad name
My name is Matteo (like User Name). Hence I had a User Folder on desktop named Matteo. Now it has a different name, but files inside are C:\Users\Matteo The wrong name is visible only on desktop How can I restore the correct name? "Allamarein" <matteo.diplomacy@gmail.com> wrote in message news:c9cd2878-a7ad-46c5-b802-a37c9d4f5b7a@d39g2000yqa.googlegroups.com... > My name is Matteo (like User Name). Hence I had a User Folder on > desktop named Matteo. Now it has a different name, but files inside > are C:\Users\Matteo The wrong name is visible only on deskto...

Exchane 5.5 IMC Naming
I need to change the FQDN display name on a 5.5 Exchange IMC. Currently the server displays netbiosname.localdomain, where it should read the FQDN of the server. I've searched the registry and misc. config files in the Exchange directory to no avail. In Exchange 2K + you can set this in the server SMTP config. However there is no place to set this in 5.5. Any ideas? Thanks for the help. We ran into a similar issue. We found our options to be either configure our reverse lookup records to our internal namespace or change the domain suffix of the smtp machine. We ended up changing ...

Create New Message Using Print Layout
I would like to draft new messages using "print layout" from the view menu. What I would really like to do is make that my default. Is there a way I can make "print layout" the default view when generating new messages? TIA Jerry What version of Outlook/Office and are you using Word as the editor? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Jerry asked: | I would like to draft new messages using "...

need registration name and key for reinstall
I purchased Money 2007 Premium in August, 2006, via Digital River download and I also ordered a backup CD. Everything worked fine until my computer died. Now I am trying to install Money on a new computer using the same backup CD. But it pops up a window entitled "Key Expired". It appears that I need a "registration name and key", "exactly as given to you". Perhaps I did receive this information before but it would now be lost on my dead computer. Is there a way to retrieve the requisite magic cookies from someone's web site? And what credentials...

Trying to understand CAsyncSocket
I want to create a TCP server using CAsyncSocket. This is my plan, please tell me if/where I am wrong. I will create a "Listener" class derived from CAsyncSocket. In my class I will call Listen. When my "listener" class's OnAccept function is called I will know that someone wants to connect to me. In the OnAccept function I will first dynamically allocate, using "new", a different "worker" class that I derived from CAsyncSocket. I pass a reference to this class as a parameter to the Accept function. At that point the "worker" class ...

Try this correction package
--cmmsnunwpw Content-Type: multipart/related; boundary="rewxxgfscrv"; type="multipart/alternative" --rewxxgfscrv Content-Type: multipart/alternative; boundary="potqqbkgxfmns" --potqqbkgxfmns Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "September 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to protect you...

OnLButtonDown coordinates relative to a GUI object ?
I have a class derived from a CStatic which is used in a dialog based app. I have overwritten OnLButtonDown in the main dialog and get the mouse click coordinates CPoint relative to the app. Assuming the user clicks on the CStatic derived object, how do I get the mouse click coordinates relative to that object ? help much appreciated, thanks. Programmer wrote: >I have a class derived from a CStatic which is used in a dialog based >app. >I have overwritten OnLButtonDown in the main dialog and get the mouse >click coordinates CPoint relative to the app. Alternatively, you could...

Creating forwarding rule in Exchange
Exchange 2003 SP2 If I have user fee@domain.com receiving email at address fee@domain.com, is it possible to create a rule in Exchange that does this: All emails from user@outsidedomain.com that go to fee@domain.com should be forwarded to foo@domain.com If it is possible, and I do not have a domain user named foo, would I have to create one or simply create a contact in order for the mailbox to be created? 1. You need some recipient to have the email address fee@domain.com 2. No native facility to selectively forward mail from a particular smtp address only. You could either do it usi...

my CStatic class : how to create a derived class
I want to derive a CStatic (let's call it CMyStatic) class to create a static box with a white background. So thanks to the classwizard I have done a new class derived from a CStatic and I have overidden the OnCtlColor message like this : CMyStatic::CMyStatic() { } CMyStatic::~CMyStatic() { } BEGIN_MESSAGE_MAP(CMyStatic, CStatic) //{{AFX_MSG_MAP(CMyStatic) ON_WM_CTLCOLOR() //}}AFX_MSG_MAP END_MESSAGE_MAP() //////////////////////////////////////////////////////////////////////////// / // CMyStatic message handlers HBRUSH CMyStatic::OnCtlColor(CDC* pDC, CWnd* pWnd, UINT nCtlColor) { H...

can't create item
can't create item, when i hit a link to e mail on a internet explorer . i get the error mesge .cant create item ...

SumIf
Hi =SUMIF(A6:A2000,"(left(a6:a2000,4))=(left($K$14,4))",F6:F2000) I want to create a sub total of all values in column F, at certain subtotal cells in column F where the the first 4 (or other to be set)characters of a code in cells a6 to a2000 match the first 4 characters in cell$K$14 (or other cell to be set). The above doesn't seem to work, is it possible? am I missing something? Any help would be greatly appreciated. Ritchi Try this array* formula: =SUM(IF(LEFT(A$6:A$2000,4)=LEFT($K$14,4),F$6:F$2000,0)) * As this is an array formula, then once you have typed it in (or s...

Check Name returns No Suggestions
To the group: Outlook 2003, Windows XP I type in a name in the To: box and click the Check Names icon and it returns 'No Suggestions' The Order of Check Names Address Lists is set to Contacts first (Address Book > Tools > Options) - kb296948 I have reset the nickname and automatic completion cache - kb287623 I have even deleted some of the auto-complete names - kb292928 Still I get 'No Suggestions' when I type in a name I have in my Contacts. Any assistance or suggestions most welcome. Thank you. John Can you give an example of what you're typing in and wh...

No name with Contacts
I'm using Outlook 2002. When I save a new contact, it appears blank in my contacts. The phone number will show but that is the only way I can identify the contact. Sometimes I get a message that the "File As" field is blank, although it isn't. Not sure what made this start happening. Those that appear in my contact list with names are fine. I can make modifications and the name still appears in the list of contacts. Only new contacts are saved with blank names, even though their File As field has a name in it. I've tried repairs, uninstall/reinstall, MS KB but not...

Passing control name to sub
I'm having trouble passing a MSFlexGrid control to a sub. The control name is "flxGalleries". The error is "Type Mismatch. Error '13'". I have the following code: Private Sub Form_Open (Cancel as Integer) ... do stuff ... GridSetup flxGalleries 'Call the Sub and pass the Control ... do more stuff ... End Sub Public Sub GridSetup (ByRef flxGrid As MSFlexGrid) With flxGrid ... do stuff ... End With End Sub What have I done wrong? Cheers Try: Call GridSetup(flxGalleries) 'Call the Sub and ...

named range scope...
i get confused... a named range can have: a global level (to the entire workbook), or a worksheet level (to the active worksheet), or a ... also, i've really looked, but can't find good information on how to define 1 level name vs. the other... (any direction on this ?) Mark, Just the two. A workbook name is created by just inserting a name in the Names box, such as myRange. A worksheet name is created by adding the sheet name to the name in the Names box, such as Sheet1!myRange. You can then also create Sheet2!myRange. You can only add a worksheet name if ...

Why does Exchange create its own profiles?
I had to recreate a user's Outlook profile, and when I went into the Control Panel, Mail, there were 35 BACKUP profiles, just called BACKUP ## or Outlook. I removed them, did the work I had to do, and checked again, and another one had been created. This one user gets this every time they open Outlook. Has anyone else come across this, or have any suggestions on what to check? We use Exchange 2003. ...

Return the name of the object I click on in a form
Hi, How do I go about returning the name of an object on a form. For example if I set the "On Click" event procedure on a rectangle object to return: MsgBox Me.Name I get the form name. What I want to do is click on the object and be abe to reference it's properties such as Tag to run other code based in these values. As always any assistance is greatly appreciated. Regards, John Dumay For the name of the control try Screen.ActiveControl.Name For the control itself use Screen.ActiveControl -- AG Email: discussATadhdataDOTcom "John Dumay" <JohnDumay@dis...