What's wrong in this query ?

[Radu]

Hi. I have a SP named, for instance, SP1.

I need to execute something like

SELECT Sum([Field1]) FROM SP1 WHERE [SP1].[Field1]='0'

and I get the message:

Server: Msg 208, Level 16, State 3, Line 1
Invalid object name 'SP1'.

However, SP1 *IS THERE* and runs fine !!!

Thanks
Alex

[Hugo Kornelis]

On 23 May 2006 20:19:56 -0700, Radu wrote:

Hi. I have a SP named, for instance, SP1.

I need to execute something like

SELECT Sum([Field1]) FROM SP1 WHERE [SP1].[Field1]='0'

and I get the message:

Server: Msg 208, Level 16, State 3, Line 1
Invalid object name 'SP1'.

However, SP1 *IS THERE* and runs fine !!!

Hi Alex,

I hope that you are not really trying to sum a character column or
comparing a numeric column to a string constant. And I also hope that
you're not really writing a query to calculate the SUM of lots of
zeroes. <g>

You can only include tables and views in a FROM clause, not stored
procedures.

Try it like this:

CREATE TABLE #temp
(Co1umn1 .....,
....)

INSERT INTO #temp (Column1, ...)
EXECUTE SP1

SELECT whatever
FROM #temp
WHERE Column1 = 0

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Radu (cu*************@yahoo.com) writes:

Hi. I have a SP named, for instance, SP1.

I need to execute something like

SELECT Sum([Field1]) FROM SP1 WHERE [SP1].[Field1]='0'

and I get the message:

Server: Msg 208, Level 16, State 3, Line 1
Invalid object name 'SP1'.

However, SP1 *IS THERE* and runs fine !!!

Sure. But you in the FROM clause you can use any of the following:

o A table
o A view.
o A table-valued function.
o A derived table. (That is a free-standing SELECT statement).
o A rowset function. (OPENQUERY, OPENXML etc)

But you cannot use a stored procedure. A stored procedure can return
0 to many result sets, and each invocation can, theoretically, yield
different results with different layout. A source in a FROM clause must
have a consistent set of columns.

Look at http://www.sommarskog.se/share_data.html for some ideas.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Cimode]

A stored procedure is not an object such as a table or a view..."select
from" apply only to objects...Hint: if possible use a view...

[Radu]

Thank you all for your answers. Now it's clear - I'm using a TEMP table
i.e. "Select .... into #Temp" instead of the subquery, and then "Select
.... from #Temp". Now it's okay.

Thank you again

Alex.