Post reply

using the result of a stored procedure in a query

  • November 18, 2011 at 9:34 am

    #260358

    hi,

    suppose that i have a stored procedure names PROC

    can i use a query like this ? :

    select p.*, t.* from PROC p join tableA t on

    p.ID= t.ID

    if no like a friend told me how to solve this type of situation efficiently

    thanks and good day

  • November 18, 2011 at 9:37 am

    #1410027

    No you can't.

    You'd need to do

    create table table

    insert into #tmp (columns)

    exec dbo.yourproc @params...

  • November 18, 2011 at 7:12 pm

    #1410262

    Hi Amigo,

    It is possible to work with a stored procedure as a dataset by using OPENROWSET but you first must enable ad hoc distributed queries:

    sp_configure 'show advanced options', 1

    reconfigure

    go

    sp_configure 'Ad Hoc Distributed Queries', 1

    reconfigure

    To demonstrate this I created a simple stored proc:

    CREATE PROCEDURE HumanResources.getEmployeeList

    AS

    BEGIN

    SET NOCOUNT ON;

    SELECT EmployeeID

    ,NationalIDNumber

    ,ContactID

    ,LoginID

    ,ManagerID

    ,Title

    ,BirthDate

    ,MaritalStatus

    ,Gender

    ,HireDate

    ,SalariedFlag

    ,VacationHours

    ,SickLeaveHours

    ,CurrentFlag

    ,rowguid

    ,ModifiedDate

    FROM AdventureWorks.HumanResources.Employee

    END

    GO

    and this query:

    SELECT *

    FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes', 'exec AdventureWorks.HumanResources.getEmployeeList')

    and you can also apply filters in a WHERE clause just as you would if you were working with a table:

    SELECT *

    FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes', 'exec AdventureWorks.HumanResources.getEmployeeList')

    WHERE ManagerId = 16

  • November 18, 2011 at 7:39 pm

    #1410264

    Ok you can, but it doesn't mean you SHOULD. 😀

  • November 20, 2011 at 1:50 am

    #1410379

    thanks bill,

    i will try and keep you informed

  • November 22, 2011 at 12:39 am

    #1411227

    amigoface (11/20/2011)

    thanks bill,

    i will try and keep you informed

    We keep forgetting one fact... (One of SSC guy corrected me few days back. It's a cycle, now I am passing it you 😀 )

    OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access.

    I would go with Remi's suggestion (if it works, I can't test it...) OR Table Valued Functions...

    Table-Valued User-Defined Functions

    http://msdn.microsoft.com/en-us/library/ms191165.aspx

  • November 24, 2011 at 5:05 am

    #1412646

    this thing want to kill me !

    i have created a table with the exact SP output parameter

    then i tried this :

    INSERT INTO tmp_spvisana

    EXEC SPVISANA

    convert(datetime, 'Jul 1 2011 11:29AM', 11),

    convert(datetime, 'Jul 1 2011 11:29AM', 11),

    5211, 5211, 1173, 1173

    and this

    INSERT INTO tmp_spvisana

    EXEC SPVISANA

    Select convert(datetime, 'Jul 1 2011 11:29AM', 11),

    select convert(datetime, 'Jul 1 2011 11:29AM', 11),

    5211, 5211, 1173, 1173

    i have errors messages like this

    Incorrect syntax near the keyword 'CONVERT'.

    NB: SPVISANA is a stored procedure that take 6 input parameters

  • November 24, 2011 at 5:07 am

    #1412647

    Declare variables, then set them and then use them to call the sp.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply