MS SQL, Using SP in Select

Hi,
I have an SP called mTest which reads like,
Create procedure mtest
as
Begin
Select * from tbSuppliers
end.

Now I want to use the SP in a select statement like

Select * from mTest.

But it is giving me error.
Can any one give me a solution for it. (If it is possible)

Thanks in advance
PillaiOriginally posted by mbpilla
Hi,
I have an SP called mTest which reads like,
Create procedure mtest
as
Begin
Select * from tbSuppliers
end.

Now I want to use the SP in a select statement like

Select * from mTest.

But it is giving me error.
Can any one give me a solution for it. (If it is possible)

Thanks in advance
Pillai

You can not call a stored procedure in a select query but insted if u just call the stored proc as
exec mtest instead of the select query and u get the same output.|||Thanks Harshal, I got the result.

Could u please tell me the difference of trusted connection and untrusted connection.

And one more Question I got while an interview is,

What all types of connections are supported by SQL?

Thanks
Pillai|||Originally posted by mbpilla
Thanks Harshal, I got the result.

Could u please tell me the difference of trusted connection and untrusted connection.

And one more Question I got while an interview is,

What all types of connections are supported by SQL?

Thanks
Pillai
for more information refer to BOL under trusted connections.
From BOL:
A login ID only enables you to connect to an instance of SQL Server. Permissions within specific databases are controlled by user accounts. The database administrator maps your login account to a user account in any database you are authorized to access.
Instances of SQL Server must verify that the login ID supplied on each connection request is authorized to access the instance. This process is called authentication. SQL Server 2000 uses two types of authentication: Windows Authentication and SQL Server Authentication. Each has a different class of login ID.
When you connect, the SQL Server 2000 client software requests a Windows trusted connection to SQL Server 2000. Windows does not open a trusted connection unless the client has logged on successfully using a valid Windows account. The properties of a trusted connection include the Windows NT and Windows 2000 group and user accounts of the client that opened the connection. SQL Server 2000 gets the user account information from the trusted connection properties and matches them against the Windows accounts defined as valid SQL Server 2000 logins. If SQL Server 2000 finds a match, it accepts the connection. When you connect to SQL Server 2000 using Windows 2000 Authentication, your identification is your Windows NT or Windows 2000 group or user account.That is a trusted connection.|||yup it is possible to use a sproc in a select statement using open query ... or opendatasource ...
------------------------
SELECT *
FROM OPENQUERY(SvrName, 'exec sproc')
------------------------

but i believe you will have to add a linked server to your own server.