By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

What's wrong in this query ?

P: n/a
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

May 24 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
May 24 '06 #2

P: n/a
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
May 24 '06 #3

P: n/a
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...

May 24 '06 #4

P: n/a
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.

May 25 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.