Solved

same using of table variable

Posted on 2002-07-01
10
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

726 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