• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

same using of table variable

Hello!

I have some stored procedure:
-------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_test]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_test]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [sp_test]
AS

declare @TableVar table (castid int NOT NULL)
declare @sql nvarchar(1000)

set @sql='select continentid from continents'

insert into @TableVar
     exec(@sql)

select * from @TableVar

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

exec sp_test
-------------------------------------------------
This statment:
insert into @TableVar
     exec(@sql)
make error.
Are there some way do it or way pass tables in stored procedures?

thank you!



0
kaspiton
Asked:
kaspiton
  • 4
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
As for the explanation of the problem, read from the books online on the "table syntax":

<...>
Remarks:
<...>
However, table may not be used in the following statements:
INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable statements.
<...>
This is because of the scope issues I guess. Calling a stored procedure or other SQL, the table variable would go out of visiblity, and this is due to internal design of the SQL engine.

Now, to workaround this, you either need to avoid the EXEC (@sql) or to put the <DECLARE @table ... INSERT INTO ... SELECT * FROM @table> into the EXEC (@sql) part (ie everything would be dynamic sql.

CHeers
0
 
kaspitonAuthor Commented:
Thank you, but can you give me some example?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Your proc could look like this:

CREATE PROCEDURE [sp_test]
AS
declare @all nvarchar(2000)
declare @sql nvarchar(1000)
set @sql='select continentid from continents'

set @all = '
declare @TableVar table (castid int NOT NULL)
insert into @TableVar ' + @sql + '
select * from @TableVar
'
exec (@all)

go


CHeers
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
kaspitonAuthor Commented:
ok, i understand, but it`s not what i mean, i need do next:
create stored procedure sp_aaa_1
    @table_name uniqueidentifier
as

declare @t table (var_id int not null)
insert into @t (var_id)
   exec ('select id from ' + @table_name)

select 1 as tag, null as parent,
c1.id as [somename!1!id],
c1.name as [somename!1!name]
from continents c1
inner join @t t on c1.id=t.var_id
for xml explicit

The xml explicit query can be very large more then 4000.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I wonder about the  uniqueidentifier data type for tablename, should that not rather by sysname?

Anyway, this could work:

CREATE PROCEDURE sp_aaa_1
   @table_name uniqueidentifier
AS
declare @all nvarchar(2000)
declare @sql nvarchar(1000)
set @sql='select continentid from ' + cast(@table_name as nvarchar(100))

set @all = '
declare @TableVar table (castid int NOT NULL)
insert into @TableVar ' + @sql + '

select 1 as tag, null as parent,
c1.id as [somename!1!id],
c1.name as [somename!1!name]
from continents c1
inner join @t t on c1.id=t.var_id
for xml explicit
'
exec (@all)
go


CHeers
0
 
kaspitonAuthor Commented:
what you mean sysname?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I mean, tablenames are either of type varchar or sysname. uniqueidentifier is a value of hexadecimal digits (0-9 and a-f), in this pattern:xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
I doubt your tablenames have these names, although not impossible.
sysname is a builtin data type, alias for nvarchar(128)

CHeers
0
 
kaspitonAuthor Commented:
ok this table will be created and deleted dynamicly,
i just need uniq. table name.
0
 
CleanupPingCommented:
kaspiton:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now