November 27, 2013 at 9:27 am
Hello,
Is the following possible?
Two stored procedures, sp1 and sp2
1-sp1 to output the result of a select query into a table type table i.e. @TT
2-sp2 to call sp1 and retrieve the result of @TT
?
Thank you
November 27, 2013 at 10:04 am
arkiboys (11/27/2013)
Hello,Is the following possible?
Two stored procedures, sp1 and sp2
1-sp1 to output the result of a select query into a table type table i.e. @TT
2-sp2 to call sp1 and retrieve the result of @TT
?
Thank you
No like you put it...
Table variables only exist inside the procedure context. You can't use a table variable @TT on sp1 and retrieve the value on sp2 since it doesn't exist on sp2.
Either you use temp tables #TT or:
CREATE PROCEDURE sp2 (....)
AS
BEGIN
...
-- declare @TT to store sp1 data
DECLARE @TT TABLE (.....)
...
-- insert into @TT sp1 result
INSERT INTO @TT EXEC sp1 ....
...
-- do whatever you want with @tt data
SELECT .. FROM @TT
..
END
Pedro
November 27, 2013 at 10:14 am
PiMané (11/27/2013)
arkiboys (11/27/2013)
Hello,Is the following possible?
Two stored procedures, sp1 and sp2
1-sp1 to output the result of a select query into a table type table i.e. @TT
2-sp2 to call sp1 and retrieve the result of @TT
?
Thank you
No like you put it...
Table variables only exist inside the procedure context. You can't use a table variable @TT on sp1 and retrieve the value on sp2 since it doesn't exist on sp2.
Either you use temp tables #TT or:
CREATE PROCEDURE sp2 (....)
AS
BEGIN
...
-- declare @TT to store sp1 data
DECLARE @TT TABLE (.....)
...
-- insert into @TT sp1 result
INSERT INTO @TT EXEC sp1 ....
...
-- do whatever you want with @tt data
SELECT .. FROM @TT
..
END
Pedro
Can not do that because it will give the error i.e. "insert exec can not be nested..."
That is why I would like to see if it is possible to use table type output.
Thanks
November 27, 2013 at 10:18 am
Can you put the full error message?
CREATE PROCEDURE sp1
AS
SELECT 1 id
GO
CREATE PROCEDURE sp2
AS
BEGIN
DECLARE @t TABLE (id INT)
INSERT INTO @t EXEC sp1
SELECT * FROM @t
END
GO
EXEC sp2
Works without any problems...
November 27, 2013 at 10:33 am
PiMané (11/27/2013)
Can you put the full error message?
CREATE PROCEDURE sp1
AS
SELECT 1 id
GO
CREATE PROCEDURE sp2
AS
BEGIN
DECLARE @t TABLE (id INT)
INSERT INTO @t EXEC sp1
SELECT * FROM @t
END
GO
EXEC sp2
Works without any problems...
Hi, I rather not go through that route because it will open a whole new topic.
But the main question here is can tabletype output parameter be used in my scenario explained above please?
Thank you
November 27, 2013 at 1:14 pm
I wrote a stored procedure that creates a temp table which I use in a calling stored procedure. You cannot declare an output variable as a user defined table type within a stored procedure. Here is the stored procedure:
ALTER procedure [dbo].[GetBackups] (
@BackupFolder varchar(300) = 'D:\SQLDumpsTrans' -- 'D:\SQLDumps'
,@BackupDays int = 0
,@BackupType varchar(3) = 'trn'
,@Debug bit = 'false'
)
as
set nocount on
declare @DirCMD varchar(8000)
declare @ID int
declare @FileName varchar(300)
declare @DateTimeModified datetime
declare @FileSize bigint
declare @Cmd varchar(8000)
declare @Fully_Qualified_File_Name_From varchar(500)
declare @Fully_Qualified_File_Name_To varchar(500)
declare @SQLDBDumpsFolder varchar(300)
declare @SQLTRNDumpsFolder varchar(300)
declare @SQLDumpsArchive varchar(300)
declare @Files BackupFiles
declare @Output table (ID int primary key identity (1,1)
,DirRaw varchar(500)
)
declare @SubDirectories table (ID int primary key identity (1,1)
,Fully_Qualified_Directory_Name varchar(max)
)
select @SQLDBDumpsFolder = SQLDBDumpsFolder
,@SQLTRNDumpsFolder = SQLTRNDumpsFolder
,@SQLDumpsArchive = SQLDumpsArchive
from dba.dbo.SQLDumpsLocation
where Active = 'true'
if @Debug = 'true'
begin
select 'Now in: GetBackups'
,@BackupFolder as '@BackupFolder'
,@BackupDays as '@BackupDays'
,@BackupType as '@BackupType'
,@Debug as '@Debug'
end
if exists (select * from tempdb.dbo.sysobjects where ID = OBJECT_ID(N'tempdb..##BackupFiles'))
begin
drop table ##BackupFiles
end
if @Debug = 'True'
select @BackupFolder as '@BackupFolder'
,@BackupDays as '@BackupDays'
,@BackupType as '@BackupType'
,@Debug as 'Debug'
set @DirCMD = 'dir ' + @BackupFolder + '\*.' + @BackupType + ' /o:d'
if @Debug = 'true'
select @DirCMD as '@DirCmd'
insert into @Output (DirRaw)
exec xp_cmdshell @DirCMD --, no_output
delete from @Output
where DirRaw like '%Volume in%'
or DirRaw like '%Volume Serial%'
or DirRaw is null
or DirRaw like '%Directory of%'
or DirRaw like '%File(s)%'
or DirRaw like '%Dir(s)%'
if @Debug = 'true'
select * from @Output
;with SubDirectory_RAW_CTE (Directory_Name_Raw) as (
select DirRaw from @Output
where DirRaw like '%<DIR>%'
)
insert into @SubDirectories (Fully_Qualified_Directory_Name)
select @BackupFolder + ltrim(rtrim(substring(Directory_Name_Raw, 40, LEN(Directory_Name_Raw)))) + '\'
from SubDirectory_RAW_CTE
union
select @BackupFolder
-- Initialize file list
;with SubDirectory_CTE (ID, DirRaw) as (
select ID, DirRaw
from @Output DRaw
where DRaw.DirRaw not like '%<DIR>%'
), SubDirectory_Details_CTE (ID
, List_ID
, ItemNumber
, Item) as (
select cte.ID
, ROW_NUMBER() OVER(partition by ID ORDER BY ItemNumber) AS 'List_ID'
, details.ItemNumber
, details.Item
from SubDirectory_CTE cte cross apply
master.dbo.DelimitedSplit8K(cte.DirRaw, ' ') as details
where isnull(details.Item,'') <> ''
), FindDatabase_CTE (ID
, DatabaseID
, Database_Name) as (
select cte.ID
,db.database_id
,LEFT(cte.Item,CHARINDEX('_Backup_20', cte.Item) - 1)
from SubDirectory_Details_CTE cte inner join
sys.databases as db on LEFT(cte.Item, CHARINDEX('_Backup_20', cte.Item) - 1) = db.name
where cte.List_ID = 5
)
--select * from FindDatabase_CTE
insert into @Files (CurrentBackupFolder
,DatabaseID
,DatabaseName
,FileSize
,FileName
,DateTimeModified
,DaysOld)
select @BackupFolder
, db.DatabaseID
, db.Database_Name
, replace(SDC_4.Item, ',', '') as [FileSize]
, SDC_5.Item as [FileName]
, cast(cast(SDC_1.Item as varchar) + ' ' + cast(SDC_2.Item as varchar) + ' ' + SDC_3.Item as datetime) as [DateTime]
, datediff(dd, cast(cast(SDC_1.Item as varchar) + ' ' + cast(SDC_2.Item as varchar) + ' ' + SDC_3.Item as datetime), GETDATE()) as [DaysOld]
from SubDirectory_CTE CTE inner join
FindDatabase_CTE db on CTE.ID = db.ID inner join
SubDirectory_Details_CTE SDC_1 on CTE.ID = SDC_1.ID
and SDC_1.List_ID = 1 inner join
SubDirectory_Details_CTE SDC_2 on CTE.ID = SDC_2.ID
and SDC_2.List_ID = 2 inner join
SubDirectory_Details_CTE SDC_3 on CTE.ID = SDC_3.ID
and SDC_3.List_ID = 3 inner join
SubDirectory_Details_CTE SDC_4 on CTE.ID = SDC_4.ID
and SDC_4.List_ID = 4 inner join
SubDirectory_Details_CTE SDC_5 on CTE.ID = SDC_5.ID
and SDC_5.List_ID = 5 left join
dba.dbo.Databases DB_Log on db.DatabaseID = DB_Log.DatabaseID
if @Debug = 'true'
begin
select 'GetBackups : @Files'
select * from @Files
end
select CurrentBackupFolder
,DatabaseID
,DatabaseName
,FileName
,DateTimeModified
,FileSize
,DaysOld
into ##BackupFiles
from @Files
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
November 27, 2013 at 5:13 pm
Try this. If you can figure out what it is doing, you should be able to make the method work for your case.
CREATE TYPE T AS TABLE (I INT);
GO
CREATE PROCEDURE SP1
AS BEGIN
DECLARE @T T;
INSERT INTO @T
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
SELECT * FROM @T;
END
GO
CREATE PROCEDURE SP2
AS BEGIN
EXEC SP1;
END
GO
DECLARE @T T;
INSERT INTO @T
EXEC SP2;
SELECT * FROM @T;
GO
DROP PROCEDURE SP2;
DROP PROCEDURE SP1;
DROP TYPE T;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply