SELECT from SP

  • Thread starter Thread starter Dimitris Nikolakakis
  • Start date Start date
D

Dimitris Nikolakakis

I have a stored procedure named [ORD-qClientsOrders] and I want to create
another SP that SELECTS records from
[ORD-qClientsOrders].

The error is 'Invalid object name ORD-qClientsOrders.'

Thanks
Dimitris
 
a) don't put special characters in your objects
b) you can't select from a sproc.

if you NEED to reuse the logic; use a UDF; table valued most likely.

tell us more about what you need to do.

in general; if you can write it as a view instead of a sproc; then do
it.
then you can select from a view without a problem

-Aaron
 
Note easy: SP return resultsets (or recordsets), not tables or views and you
cannot select from a resultset directly. To do so, you must use OPENQUERY,
OPENROWSET, or OPENDATASOURCE to call the SP from another SP. These
procedures requires that you use a connection string to the server itself or
that you create a linked server, also pointing toward itself. See the BOL
for more info on these three functions.

The easiest and best solution for you is probably to convert your SP to a
View or a User Defined Function (UDF), which may be called directly from a
Select. Also, with UDF, you must often provide the full name including the
schema; ie. use dbo.MyUDF() instead of just MyUDF().

On the ADP side, to call a UDF as the record source of a form, you must use
a full SQL statement and not just the name (as you would do for a SP):

Select * from MyUDF()
 
and from what i've seen; i try to always prefix functions with the
dbo.. it seems to get a little bit testy otherwise

-Aaron

Note easy: SP return resultsets (or recordsets), not tables or views and you
cannot select from a resultset directly. To do so, you must use OPENQUERY,
OPENROWSET, or OPENDATASOURCE to call the SP from another SP. These
procedures requires that you use a connection string to the server itself or
that you create a linked server, also pointing toward itself. See the BOL
for more info on these three functions.

The easiest and best solution for you is probably to convert your SP to a
View or a User Defined Function (UDF), which may be called directly from a
Select. Also, with UDF, you must often provide the full name including the
schema; ie. use dbo.MyUDF() instead of just MyUDF().

On the ADP side, to call a UDF as the record source of a form, you must use
a full SQL statement and not just the name (as you would do for a SP):

Select * from MyUDF()

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Dimitris Nikolakakis said:
I have a stored procedure named [ORD-qClientsOrders] and I want to create
another SP that SELECTS records from
[ORD-qClientsOrders].

The error is 'Invalid object name ORD-qClientsOrders.'

Thanks
Dimitris
Click to expand...
Click to expand...
 
in other words, select * from dbo.MyUdf()

I would shy away from functions though; they're a little bit difficult
to work with; and i've had some compatability problems sometimes with
them.

I would reccomend the view route.

build a view the combines orders and orderDetails.. then when you
'select * from myOrders where OrderID = 12' then it usually does a
pretty good job internally

-Aaron

Note easy: SP return resultsets (or recordsets), not tables or views and you
cannot select from a resultset directly. To do so, you must use OPENQUERY,
OPENROWSET, or OPENDATASOURCE to call the SP from another SP. These
procedures requires that you use a connection string to the server itself or
that you create a linked server, also pointing toward itself. See the BOL
for more info on these three functions.

The easiest and best solution for you is probably to convert your SP to a
View or a User Defined Function (UDF), which may be called directly from a
Select. Also, with UDF, you must often provide the full name including the
schema; ie. use dbo.MyUDF() instead of just MyUDF().

On the ADP side, to call a UDF as the record source of a form, you must use
a full SQL statement and not just the name (as you would do for a SP):

Select * from MyUDF()

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Dimitris Nikolakakis said:
I have a stored procedure named [ORD-qClientsOrders] and I want to create
another SP that SELECTS records from
[ORD-qClientsOrders].

The error is 'Invalid object name ORD-qClientsOrders.'

Thanks
Dimitris
Click to expand...
Click to expand...
 
Back
Top