Solved

same using of table variable

Posted on 2002-07-01
10
367 Views
Last Modified: 2008-03-03
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
Comment
Question by:kaspiton
  • 4
  • 4
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7121354
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
 

Author Comment

by:kaspiton
ID: 7121380
Thank you, but can you give me some example?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7121389
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:kaspiton
ID: 7121401
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7121404
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
 

Author Comment

by:kaspiton
ID: 7121408
what you mean sysname?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 7121416
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
 

Author Comment

by:kaspiton
ID: 7121420
ok this table will be created and deleted dynamicly,
i just need uniq. table name.
0
 

Expert Comment

by:CleanupPing
ID: 9280210
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question