2 Replies - 1023 Views - Last Post: 30 May 2015 - 03:25 PM

#1 kyle_denney   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 171
  • Joined: 10-August 12

How to access field in another table within stored procedure

Posted 09 April 2015 - 10:32 AM

Hello,

I am trying to run the below code but run into the 'Multi-part Identifier could not be bound' error. I think it is due to trying to access a database table from a separate database but it is on the same server. Any ideas?

SELECT DISTINCT
@ActiveStudents2 = COUNT([ActivityHistory].[dbo].[tblActivityCounts].[id])
FROM  dbo.tblSchools INNER JOIN
dbo.tblStudentSchool ON dbo.tblSchools.schoolid = dbo.tblStudentSchool.schoolid INNER JOIN
dbo.tblStudentPersonal ON dbo.tblStudentSchool.id = dbo.tblStudentPersonal.id
WHERE dbo.tblStudentSchool.schoolid IN (@tempschoolid) AND tblStudentSchool.graduationyear IN (SELECT Items FROM FN_Split(@gradyears, ',')) AND ([ActivityHistory].[dbo].[tblActivityCounts].[datetimechanged] >= @datefrom and [ActivityHistory].[dbo].[tblActivityCounts].[datetimechanged] <= @dateto)								



The error occurs when I try to access the tblActivity Counts table in the Activity History database which is a separate database. I even try running this as the sa and it doesn't work. There aren't any spelling errors. Any help is appreciated. Thank you!

Is This A Good Question/Topic? 0
  • +

Replies To: How to access field in another table within stored procedure

#2 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: How to access field in another table within stored procedure

Posted 09 April 2015 - 10:45 AM

you can't access a table unless you include it in your FROM Clause or an inner query. If you try and do what you are doing, how does the Engine know what row to use in your COUNT and WHERE Clauses.

This post has been edited by rgfirefly24: 09 April 2015 - 10:46 AM

Was This Post Helpful? 0
  • +
  • -

#3 November-06   User is offline

  • D.I.C Regular

Reputation: 54
  • View blog
  • Posts: 482
  • Joined: 04-January 11

Re: How to access field in another table within stored procedure

Posted 30 May 2015 - 03:25 PM

You are not even including any column from the tables you are reading in your select statement. What's the point of all these querying?

Then you are getting a column from a table that is not part of the tables you are querying on.

You should rethink your objectives. What is your desired output? What are the tables concerned? What are the conditions that need to be considered? How can the desired output be achieved?

Can't you just query directly to tblActivityCounts? Or, maybe, if possible, tblActivityCounts should be part of the tables being joined.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1