Solved

same using of table variable

Posted on 2002-07-01
10
357 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 142

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 142

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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 142

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 142

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now