none
select from stored procedure, possible?

    Question

  • hi everyone,
    in SQLEXPRESS if I execute

    'use SqlTrackingDb exec dbo.GetName'

    I can see all entries in GetName.
    But I do not know if the following select statement is possible for a stored procedure in e.g. visual studio:

    select * from dbo.GetName where Id = "001";

    Any ideas?
    Thanks in advance,
    best regards bg



    Friday, April 20, 2007 9:10 AM

Answers

  • That would only work IF the stored procedure was designed to accept incoming parameters.

     

    If you are the one writing the stored procedures, refer to Books Online, Topic: Stored Procedures.

    Friday, April 20, 2007 6:49 PM

All replies

  • That would only work IF the stored procedure was designed to accept incoming parameters.

     

    If you are the one writing the stored procedures, refer to Books Online, Topic: Stored Procedures.

    Friday, April 20, 2007 6:49 PM
  • hi,

     sider wrote:
    hi everyone,
    in SQLEXPRESS if I execute

    'use SqlTrackingDb exec dbo.GetName'

    I can see all entries in GetName.
    But I do not know if the following select statement is possible for a stored procedure in e.g. visual studio:

    select * from dbo.GetName where Id = "001";

     

    usually you should consider defining a user defined function for that task..

     

    regards

    Friday, April 20, 2007 10:36 PM
  • Hi Andrea,

    I agree that using a user function to perform this job is the right direction. However I face a serious problem trying to go with this direction when the user function itself is complex and requires a use of a stored procedure inside it, for example sp_executesql.

    As you know, if a user function is defined to retun tabular result, it is imperative that it will not change the DB and therefore usage of stored procedures is not allowed. Example:

     

    CREATE FUNCTION dbo.EmployeeByID(@InEmpID int)

    RETURNS @retFindReports TABLE

    (

    -- columns returned by the function

    EmployeeID int NOT NULL,

    Name nvarchar(255) NOT NULL

    )

    AS

    -- body of the function

    BEGIN

    INSERT @retFindReports

    EXEC GET_EMPLOYEE_DATA @InEmpID

    RETURN

    END

    GO

     

    The following script will fail to execute with the following error:

    Msg 443, Level 16, State 14, Procedure EmployeeByID, Line 11

    Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.

     

    My question is - is there a way to tell SQL parser that the proposed stored procedure don't have any side-effect so it can be used inside a user function?

     

    Many thanks,

    Busi

    Tuesday, May 29, 2007 2:51 PM
  • hi Busi,

     baruchl wrote:

    Hi Andrea,

    I agree that using a user function to perform this job is the right direction. However I face a serious problem trying to go with this direction when the user function itself is complex and requires a use of a stored procedure inside it, for example

    sp_executesql.

     

    please avoid, whenever possible, to use dynamic SQL, as you already know all the security troubles it implies..

     

    As you know, if a user function is defined to retun tabular result, it is imperative that it will not change the DB and therefore usage of stored procedures is not allowed. Example:

     

    CREATE FUNCTION dbo.EmployeeByID(@InEmpID int)

    RETURNS @retFindReports TABLE

    (

    -- columns returned by the function

    EmployeeID int NOT NULL,

    Name nvarchar(255) NOT NULL

    )

    AS

    -- body of the function

    BEGIN

    INSERT @retFindReports

    EXEC GET_EMPLOYEE_DATA @InEmpID

    RETURN

    END

    GO

     

    The following script will fail to execute with the following error:

    Msg 443, Level 16, State 14, Procedure EmployeeByID, Line 11

    Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.

     

    My question is - is there a way to tell SQL parser that the proposed stored procedure don't have any side-effect so it can be used inside a user function?

    nope, you can not circuinvent it...

     

     

    I do not know if the posted code is just an example just to explain your point, so perhaps I'm missing something, but why don't just use

    EXEC GET_EMPLOYEE_DATA @InEmpID

    as you already defined instead of using a function returning the same data the procedure does?

    regards

    Tuesday, May 29, 2007 3:45 PM
  • Hi Andrea,

    thanks for your reply. As you understood, the example was just a simple example to stress my point, therefore it's won't work.

    However, I think I've found a way to overcome the issue:

     

    CREATE PROCEDURE myProc

    @run_id uniqueidentifier

    AS

    create table #tb (plist_id int, pattern_id int, order_id int)

    insert #tb exec dbo.SNG_GET_PATTERNS_GLOBAL_ORDER_SP @run_id

    select * from #tb

    drop table #tb

    GO

     

    EXECUTE myProc 'B299D866-FA00-441D-ABC3-4F35494D9208'

     

     

    Basically what I want to do is to join between resultsets I get from different stored procedures, so what I will do know is to wrap all the stored procedures calls in one stored procedure, insert all the SP resultsets into local tables and do the join between the local tables.

     

    Thanks,

    Busi

    Wednesday, May 30, 2007 7:42 AM
  • hi Busi,

    yep, this is a viable solution..

    regards

    Wednesday, May 30, 2007 10:40 AM
  • Is there a way to do Select Into version of the statement?...

    create table #tb (plist_id int, pattern_id int, order_id int)

    insert #tb exec dbo.SNG_GET_PATTERNS_GLOBAL_ORDER_SP @run_id

    select * from #tb

    drop table #tb

    GO


    Tuesday, September 23, 2008 8:59 PM
  • No, that is not possible.

     

    EXECUTE() or sp_executesql operate in a different 'scope' from the calling code.

     

    In the example Andrea provided, the #Temp table is prepared BEFORE the EXECUTE() statement, so the scope of the EXECUTE() statement has access to the #Temp table, AND the #Temp table will continue to exist in the calling scope after the EXECUTE() completes.

     

    Attempting to use a SELECT ... INTO will not be a satisfactory solution BECAUSE, the #Temp table will be created in the scope of the EXECUTE(), and when the EXECUTE() completes, temporary objects created in the EXECUTE() scope will no longer exist when the EXECUTE() scope is exited. The #Temp table will 'disappear' to the calling code.

    Tuesday, September 23, 2008 9:14 PM
  • Try this:

    create table #test(
      id varchar(3),
      therest whatever,
      ...
      ...
    )

    insert into #test exec dbo.GetName

    select * from #test where Id = "001"
    Thursday, January 28, 2010 9:21 AM