Passing user-defined table via openrowset to storedprocedure

All Replies

• Sunday, February 17, 2013 8:41 AM

  Answerer

   

   
  

  1

  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
  

  • Reply
  • Quote

   
• Sunday, February 17, 2013 10:08 AM

   

   
  

  0

  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

  • Reply
  • Quote

   
• Sunday, February 17, 2013 3:30 PM

   

   
  

  0

  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
  • Reply
  • Quote

   
• Monday, February 18, 2013 7:16 AM

   

   
  

  0

  Dear Erland,

  I'm going to read it

  Thanks

  • Reply
  • Quote

   
• Monday, February 18, 2013 8:19 AM

   

   
  

  0

  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]

  
  

  • Edited by Saman Kefayatpour Monday, February 18, 2013 8:20 AM
  •  
  • Reply
  • Quote

   
• Monday, February 18, 2013 2:59 PM

   

   
  

  0

  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

  • Marked As Answer by Iric WenModerator Monday, February 25, 2013 9:36 AM
  •  
  • Reply
  • Quote