none
Passing user-defined table via openrowset to storedprocedure

    Question

  • I want to call stored procedure (Sp1) which has user-defined table (T1) as input parameter. Stored procedure (Sp1) in itself Exec another stored procedure so I forced to call it(Sp1) by "OPENROWSET". But I can't pass T1 to Sp1 via "OPENROWSET"!!! All of the stored procedures are in the same database. (I'm using SQl Server 2008).

    So it anybody can help me via this?

    Thanks in advance


    Sunday, February 17, 2013 7:58 AM

Answers

  • Yes, and as I discuss in my article, INSERT-EXEC has several problems, as the "cannot be nested" is one of them.

    Also, I as discuss in my article, this piece of code:

        Set @sql='SELECT *
    FROM OPENROWSET(
                   ''SQLNCLI'',
                   ''Server=SERVERNAME;Trusted_Connection=yes;'',
                   ''set fmtonly off; exec DBName.dbo.ProcMine_B @t=' +convert(varchar(10),2) + ''')'

    Will not work on SQL 2012. Or more precisely, the use of SET FMTONLY OFF will have any effect.

    You have all reason to study the solutions in my article and rework your code along of any of the lines I present: sharing temp tables, sharing a process-keyed table, XML or even the CLR solution.

    And that is not only because of the table-valued parameter, but because the solution you have today is not a sound one.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 18, 2013 2:59 PM

All replies

  • Why OPENROWSET? Sorry, cannot test it right now.

    create type tt_example AS TABLE
     (spid int)
    go
    create procedure sp1
     @spids tt_example READONLY
    AS
     SELECT *
     FROM @spids
    GO

    create procedure sp2

    as

    declare @spids tt_example
    insert into @spids
    select top 10 spid
    from sys.sysprocesses

    exec sp1 @spids=@spids

    ----exec sp2


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, February 17, 2013 8:41 AM
    Answerer
  • Dear Uri Dimant,

    Thanks for your answer

    I can't use exec because in my stored  procedure( for example sp2) I call a stored procedure (for example sp1)  which in it, it (sp1) call another stored procedure by exec, so if I use exec the SQL Server gets me the nested exec error.

    Regards,

    Saman

    Sunday, February 17, 2013 10:08 AM
  • You should noy use OPENROWSET or OPENQUERY to retrieve data on the same server. It's expensive and brittle.

    It is not clear to me what you exact scenario is, but I have an article on my web site entitled How to Share Data between Stored Procedures where I discuss a number of alternatives, including OPENQUERY/OPENWROWSET.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, February 17, 2013 3:30 PM
  • Dear Erland,

    I'm going to read it

    Thanks

    Monday, February 18, 2013 7:16 AM
  • Dear Erland,

    The problem is how to pass input table which is a User defined table to [ProcMine_B]

    AS you know if I use "exec ProcMine_B" in [ProcMine_BBBB] I get an error like

    'An INSERT EXEC statement cannot be nested.'                     

    create Proc [dbo].[Proc2]
    @r int
    As
    Begin
     SET NOCOUNT ON
          Select @r,2,3,'test'
    End

    go

    CREATE Proc [dbo].[ProcMine_B]
    @t as int,
    @inputTable tt_example READONLY
    As
    Begin
     SET NOCOUNT ON
          Declare @table table (col1 int,col2 int,col3 int,col4 nvarchar(max))

          insert into @table     
          exec Proc2 1

       select * from @table as t inner join @inputTable as i on i.id=t.col2
    End

    go

    CREATE Proc [dbo].[ProcMine_BBBB]
    As
    Begin
     SET NOCOUNT ON
     BEGIN TRANSACTION
     Declare @table table (col1 int,col2 int,col3 int,col4 nvarchar(max))

        Declare @sql nvarchar(max)

        Set @sql='SELECT *
    FROM OPENROWSET(
                   ''SQLNCLI'',
                   ''Server=SERVERNAME;Trusted_Connection=yes;'',
                   ''set fmtonly off; exec DBName.dbo.ProcMine_B @t=' +convert(varchar(10),2) + ''')'

    -- Print @sql
            Insert @table(col1,col2 ,col3,col4)
            Exec(@sql)    
            select * from @table
       COMMIT TRANSACTION
    End


    execute [dbo].[ProcMine_BBBB]


    Monday, February 18, 2013 8:19 AM
  • Yes, and as I discuss in my article, INSERT-EXEC has several problems, as the "cannot be nested" is one of them.

    Also, I as discuss in my article, this piece of code:

        Set @sql='SELECT *
    FROM OPENROWSET(
                   ''SQLNCLI'',
                   ''Server=SERVERNAME;Trusted_Connection=yes;'',
                   ''set fmtonly off; exec DBName.dbo.ProcMine_B @t=' +convert(varchar(10),2) + ''')'

    Will not work on SQL 2012. Or more precisely, the use of SET FMTONLY OFF will have any effect.

    You have all reason to study the solutions in my article and rework your code along of any of the lines I present: sharing temp tables, sharing a process-keyed table, XML or even the CLR solution.

    And that is not only because of the table-valued parameter, but because the solution you have today is not a sound one.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 18, 2013 2:59 PM