[b]Invalid Object Name 'dbo.MyTableName'[/b] 1

[jlitondo]

I receive the above error even when I manually execute a stored procedure that is joined to a table valued function. I confirmed in sysusers table that dbo is the owner of both objects. What could be wrong with the following statement? The error I get lists the function, [Resolved Issues], as the invalid object yet I can still run it stand alone.

ALTER PROCEDURE dbo.Divisions
AS
SELECT dbo.Divisions.divisionID, dbo.[Resolved Issues].NumResolved
FROM dbo.[Divisions]
LEFT JOIN dbo.[Resolved Issues]
ON
dbo.[Divisions].divisionID = dbo.[Resolved issues].divisionID

 

[jlitondo]

Since I'm running the above procedure in msde through an adp, I decided to view it in the design grid and noticed the function name was repeated twice. Deleting one instance of the name made the procedure run fine but checking back in the design grid, deleted instance of the function name was back. The procedure still runs fine but what's the difference? Any ideas?

 

[SQLBill]

What do you mean by "I can still run it [as a] stand alone"?

Is your Procedure name really a table name (dbo.Divisions)? I wouldn't think that's good programming.

By the way, you could save some typing by using alias'.

ALTER PROCEDURE dbo.Divisions
AS 
SELECT  D.divisionID, RI.NumResolved
FROM    dbo.[Divisions] D
        LEFT JOIN  dbo.[Resolved Issues] RI
        ON 
        D.divisionID = RI.divisionID

-SQLBill

 

[jlitondo]

Hi SQLBill,
Actually my stored procedure name is spr_selResolvedIssuesByDivision, I just thought I'd keep the names short for the post.
I'm in the process of learning how to write stored procedures so excuse my cumbersome coding. By running stand alone I meant that if I double click on the function for example, it would run fine. The problem arose when the function was called from within the stored procedure.
By the way, thanks for the tip on using aliases, that really saves on typing.

 

[SQLBill]

So then, [Resolved Issues] is a function and not a table?

I don't believe you can use it that way (as a table). I'll check more.

Thanks for the star for my tip.

-SQLBill

 

[jlitondo]

Yes, [Resolved Issues] is a function which also has a different name from what i've posted. I was able to use it this way after adding () to the the function name so that the join part of the expression looks like this.

LEFT JOIN dbo.ResolvedIssues() RI

I'm not sure yet if this is the best way of doing this.

On a slightly different note, I have an aliased column name in the select portion of the query. How would I group the returned set against this column, using the aliased name? The column is aliased because I'm performing a complex calculation against it within the select statement.
The procedure looks roughly like this:

ALTER PROCEDURE dbo.Divisions
@EnterDate as DateTime
AS
SELECT D.divisionID, daysOld =
CASE WHEN (DateDiff(d, @EnterDate,D.entryDate ) * - 1 < 31) THEN '0-30' ELSE '---' END

FROM dbo.[Divisions] D
LEFT JOIN dbo.[Resolved Issues] RI
ON
D.divisionID = RI.divisionID

The procedure won't allow me to group against daysOld.