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
1268 Views

Similar Articles

[PageSpeed] 26

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:

Largest advisable single mail store in Ex 2k Enterprise?
I have 150 users in one Exchange 2000 Enterprise server. I'm trying to convince management that dividing our 80GB Information Store into 3 smaller stores for quicker backup, restore and smaller downtime windows when ESEUTIL and ISINTEG need to be run (I only run them when told to do so by MS Support). Right now if I had to run ESEUTIL and ISINTEG it'd probably take 2 days to run these utilities until there were no errors. I'd rather take one store down while the others remain online and get this done overnight. I need some ammunition to further my case, as folks outside if IT are ...

Schema for an object inherits from CollectionBase
Hi, I'm trying to create the schema (XSD) for a class that inherites from System.Collections.CollectionBase, and indicate the ElementName of the root and every item. This is an example: [XmlRoot("list")] public class MyList : System.Collections.CollectionBase { (...) public MyListItem this[int index] { get { return this.List[index] } set { this.List[index] = value; } } (...) } And I want an xml like this: <list> <listItem></listItem> </list> But I get the following: <list> <MyListItem></MyLis...

Trying to calculate the average cost per minute of cell phone usage
I have a spreadsheet that I�m trying to use to calculate the cost per minute of cellular calls. I have 3 plans (in rows 3, 4, and 5) that each provide a monthly cost (column a), and the number of minutes included in that cost (column b). There is also a cost per minute if you go over that allowance (cell E2). I have a field (cell C7) that I use to enter the average number of minutes used. I have a drop down list (cell C8) that allows you to select the plan you have. I have a formula that is currently written as: =SUM(((C7-C8)*E2)+A5)/C7 where: C7 = average number of minutes...

How to correct wrong selected bank name
I selected Chase Credit Cards (aka Bank One Visa) in error - it should have been Chase Credit Cards. How can I correct this? I keep getting back to the aka entry. Bob In microsoft.public.money, BobTucson wrote: >I selected Chase Credit Cards (aka Bank One Visa) in error - it should have >been Chase Credit Cards. How can I correct this? I keep getting back to the >aka entry. Disable Chase for online access. Go to the details for the account and change the Financial Institution. Set it up for online access again. ...

Invalid File Format
When I start Excel I get an initial message that says ssipro97.dot is an invalid format. Any idea why this would come up? It doesn't seem to affect how Excel works, it's just annoyint. ...

Invalid syntax
I get the error invalid syntax for the following line : ( summing up figures in case GetBonus = Yes) Bonus : iif(getBonus = -1;nz(BonusL1;0)+ NZ([BonusL2];0)+ NZ([BonusL3];0) could you help ? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200712/1 Ditch the semicolons. iif( Formula to test,results if true, results if false.) I would guess you want iif(getBonus = -1,nz(BonusL1;0)+ NZ([BonusL2];0)+ NZ([BonusL3],0) peljo via AccessMonster.com wrote: > I get the error invalid syntax for the following line : ( summing up figures...

Create a script to automatically save received email...
Hi all, I'm trying to create a script that I can assign to a Rule that automatically saves the email based on Subject content as a txt file to a specified network folder. The Rule part is easy... but the part that has me scratching my head is the script! To do this would be an amazing help!!! Thanks in advance for any help. Ian. On Mon, 18 Apr 2005 04:49:03 -1000, IanFW <IanFW@discussions.microsoft.com> wrote: > Hi all, > I'm trying to create a script that I can assign to a Rule that > automatically > saves the email based on Subject content as a txt file to ...

Graphics problem on website created in Publisher
I'm designing a simple 6 page website. My home page graphics look great but the same borders and logos on the other pages will not show up. When I look at their properties they are pointing to the C: drive folder instead of the Home_files folder I loaded on FTP. I've tried to change the code but it doesn't appear that I'm able to do that. Can anyone help? Move all images and other design elements off of the Master Page onto the main page. (View > Master page). In general limit the use of the Master Page feature to print publications and not web publications. ...

How to XMLSerialze an object which has an object array as filed?
Hi, Could anyone give me some help about the following problem? I need to XMLSerialize an object of the following class: [Serializable] public class FunctionCallingList { public string typeName; public string methodName; public DateTime time; public object[] args; } And the element of args here could be different(maybe 1 int, 1 doulbe, 1 array and 1 user define type object, which may has another object arrray inside). But when I use the following code: FunctionCallingList = new FunctionCallingList(...); xmlSerializer = myGetXmlSerializer(typeof(fcl)); xmlSeri...

Multi Company mail store
Hi, I would like multi company mail store in exchange 2000 server. Example abc.com and xyz.com and tyu.edu and etc is there any way one exchange in do it the configuration and how can i the conf. Please send meterial for this. Thanks a lot Hello, Try MS KB Article 262183 : Hosting with Microsoft Exchange 2000 Server HTH Mark "Tolgay G�l" <tolgay@veezy.com> wrote in message news:OmfLw4CqDHA.1084@tk2msftngp13.phx.gbl... > Hi, > > > I would like multi company mail store in exchange 2000 server. Example > abc.com and xyz.com and tyu.edu and etc > is t...

invalid handle
hi frds i m very new in vc++ and i m founding too much difficulty in this so plz can any one solve this problem .... actully i m inserting a image in list box using this code ........ everything is returning write thing still it is saying invalid handle after ImageList_Add(hList,m_hBmpNew,0); when i m going to dibug it plz help me BOOL Fun() { // Create 256 color image lists HIMAGELIST hList = ImageList_Create(32,32, ILC_COLOR8 , 8, 1); HBITMAP m_hBmpNew = (HBITMAP) LoadImage( AfxGetInstanceHandle(), // handle to instance "c:\\img.bmp", /...

Invalid Property Value
I have a macro that uses an input form to populate a spreadsheet. There are various validations that run prior to the data populating. On one validation if an incorrect choice has been made from a drop down box a error message prompts the user to select again form the drop down box and will not populate until a valid selection has been made. I am now getting an Excel warning message (exclamation message) saying 'Invalid Property Value' after the spreadsheet has populated which only happens if an invalid selection has been made and corrected from the drop down box. If the ...

OFX
When doing an update from my broker I get the message cannot establish secure communication with the server. Digital certificate is invalid. OFXIE12038. This is new happening in last month. Broker support says problem is Money 2002 not supported so I installed Money 2005 (with some qualms) and of course, still get the same message. I can access the brokerage website OK from Money, just cannot get statement downloads. -- Bill ...

error when trying to restore Money 01 backup after hard drive crash
I recently had to replace my hard drive. I installed Money (04 Deluxe) and tried to restore my Money back up that was on an external hard drive. I got this error: "Please insert next backup disk. (The disk containing file "".)" Probably the most useless error message ever since a) i'm pretty sure this is the entire backup and b) it doesn't tell me what file it's talking about. I realized that my previous Money file might have been from Money 2001 Standard so I uninstalled Money 04, installed Money 01, and tried to restore the file again. Same error message. ...

Dynamic create object in a SDI application
I tried to create a CEdit object in myView:OnCreate(..), rather using draw a Edit Box in a dailog(I want the Edit object been created at run time). I can do it successfully & can set some property on this object, but I can't find how to set the object's appearance to be 3D-looking. (just like set Border in Style + set Static-Edge in ExtendedStyle) Is there anyone can help ? Thanks & Regards, "EagleChen" <eagle@magination.com.tw> wrote in message news:eiOQL%23chDHA.1872@TK2MSFTNGP10.phx.gbl... > I tried to create a CEdit object in myView:OnCreate(..), ra...

Assigning VB to Grouped Objects
I am operating Excel 2003 and use grouped objects as in-sheet controls (i.e. the objects are assigned to VB procedures). The general methodology works fine, except with Excel 2003 I cannot check what macro is assigned to the control. By default, all grouped objects default to an event macro assignment of “<group name>_click”. If you assign the group a macro, the one you assign is connected to the object and runs when you click it. But, if you want to check the assignment (i.e. right click > assign macro) it shows the default event macro and not the one you have assigned…! For...

Problem in creating a company
Hi, I am getting the following error I have a company in service pack two but the other companies are not getting upgraded to service pack II, i am getting an sql error which shows the version of one database is different from another.. This is the error i am getting A product installed on your computer is on a different version than the database version, you will not be able to use the application until this issue is resolved. Any help is appreciated Regards Reddy Well, first, all db's should be upgraded. But the error message means that there is a 3rd party product or GP Addo...

storing a string into a xmlelement and vice versa
Hi All, this is a string "<SXPServerGetObjectsResult><Objects><Task><District Key="258627584">NL KPN</District></Task></Objects></SXPServerGetObjectsResult>" i want to store this into a xmlelement variable for further processing this as xmlelement How can i do it? may u also tell me vicevers i.e. xmelement to string? Any help would be appreciated by me. Thanks, Deepak kr_deepak123@hotmail.com kr_deepak123@yahoo.co.in deepak wrote: > this is a string > > "<SXPServerGetObjectsResult><Objects&g...

Outlook 2002: Error -2147024891 Collaboration Data Objects E_ACCESSDENIED (80070005)
When I tried to debug a program written using VB, which intend to send an email attachment, I get the following error message. Error -2147024891 : [Collaboration Data Objects - [ E_ACCESSDENIED (80070005)]] The email attachment is getting saved in the Outbox of Microsoft Outlook thus not allowing the user to select the recipient list from TO, CC & BCC lists. Some parts of the codes are stated below for your information ........... .............. Set objSession = CreateObject("Mapi.Session") ........... .............. objSession.Logon "MS Exchange Settings&qu...

Correct way of changing MFC application name (m_pszAppName)
As far as I understand, the MSDN documentation regarding how to change CWinApp::m_pszAppName is incorrect. Following the documentation can lead to memory access violation errors. Additionally, the KB article 154744 also gives wrong advise about how to change m_pszAppName. Here's why: At the very beginning of application initialization, AfxWinInit calls CWinApp::SetCurrentHandles, which caches the current value of the m_pszAppName pointer as follows: pModuleState->m_lpszCurrentAppName = m_pszAppName; That is, the module state struct holds a copy of the m_pszAppName pointer. Now, if y...

Invalid Procedure Call on Forms
Hello, any suggestions are appreciated! I imported tables from our media database into our membership database. I was going to combine the information so that I could streamline orders with lookup fields but my supervisor didn't like it so... I went back to the media database and now NONE of the form buttons are working. The forms open, and all the reports, queries, and tables open without incident. However, when I click any button on any form (to call up a report, query or another form), I receive an error: "Invalid Procedure Call or Arguement". I tried creating a...

Exchange 2000 mailbox shows another user name other than the owner's name
Hi; When I go to the mailbox in System Manager of Exchange 2000, I found that some mailboxes which are accessed or logged on by another user name. For example; the "Last Logged on By" column for mailbox "Sam" showing logged on by John. I checked the mailbox of Sam, he did not share his mailbox with John. Why it displays like that? I am using Veritas to backup Exchange, but I used a system account to logon mailbox for backup. What is the other factor? Thanks! ...

550 relaying mail to <domain name> is not allowed
I keep getting this message generated by the system administrator when I try sending emails to some email adddresses. Anyone know why and where I can find a fix? John You probably need to authenticate to your outgoing server. You can set this up on the "Outgoing Server" tab for your account. -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "Joh" <jrwalton@talk21.com> wrote in message news:028601c35cc4$222c7470$a001280a@phx.gbl... > I keep getting this message generated by the system...

RegOwner and RegCompany Objects of Win32_Product Class
I am using RegOwner and RegCompany Objects of Win32_Product Class to get the Product details of the Microsoft applications installed on my machine. I was using the code below StrComputer = "." Function DispList(StrComputer) DIM objFSO, objWMIService, colSoftware, objSoftware Set objFSO = CreateObject("Scripting.FileSystemObject") DIM OutputFile DIM objFile OutputFile = "D:\output.txt" Set objFile = objFSO.CreateTextFile(OutputFile) objFile.WriteLine "Software list for " & StrComputer Set objWMIService = GetObject("wi...

Another Method for Parsing Names
Having bothered others with ways of parsing names, I thought I'd at lease contribute a simple solution for doing the complete job... Assume you have names of the following types in column C Mrs Dorothy Hannity Dr P R Rogers Dana Delany Mr Bradley K Pitts Type the following formulas into the specified cells: O1=FIND(" ",C1) Determines the location/existence of the blank following the Salutaton or First Name P1=FIND(" ",C1,FIND(" ",C1)+1) Determines the location/existence of the blank following the First Name ...