Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: 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
    Pillai
    Last edited by mbpilla; 02-11-04 at 05:54.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: MS SQL, Using SP in Select

    Originally 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.

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    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

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    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.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •