Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

same using of table variable

Posted on 2002-07-01
10
Medium Priority
?
376 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 400 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

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!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

636 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