Passing a resultset from one SP to another
-
If one SP does a simple select like SELECT * FROM authors, which returns a resultset, can I pass that resultset as an input to another SP without using a temporary table or resorting to external code?
Basically, I have two SP's, ClientExport and ClientImport, these are called by .net code which passes the output of one to the input of another after converting it to XML. I want to test the SP's without having to use the .net code all the time as it was designed to transport the data over slow unreliable network connections and not DB to DB.
-
I am not aware of a way to do exactly what you are asking, but I think that there may be a work around.
If your SP is a simple select statement, why not incorporate the arguments of both SP's and create one sp to do the job.
Steve
-
The SP's have to be independent because they are designed to live on different machines hundreds of miles away. Ultimately they will, I just want an easy way to test them before they go into production.
-
Well,
You cannot pass record sets between stored procedures.
You ultimately will have to either use a temp table in the calling stored procedure, or use an openDataset or Openquery command
use temp table
-- Columns in temp table must match columns returned from stored procedure
Create table #Temp(col1 int, col2 char(5) .....)
insert into #Temp(col1, col2, ...)
exec dbo.Myprocedure @param1, @Param2
or embed the query from remote data using commands below.
From BOL
OPENDATASOURCE
Provides ad hoc connection information as part of a four-part object name without using a linked server name.
Syntax
OPENDATASOURCE ( provider_name, init_string )
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.CategoriesOPENQUERY
Executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.
Syntax
OPENQUERY ( linked_server , 'query' )
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
-
I love for somone to tell me that something cannot be done. It stimulates the thinking.
It occurs to me that table is a type of variable such that
declare @t table(some table definition) -- this is a valid statement.
If therefore you can use this type of declaration in a SP argument declaration, then it would stand to reason that you could pass it to another stored proc.
I haven't tested this theory, thought about the implication or reasearched to see what is happening under the covers, but it seems like this might be a viable path.
Steve
-
Boy, the ideas just keep comming.
You could possibly seralize the data into xml (ie Select ... for xml) and pass the data like that. Of course if your data exceeded 8000 chars, then you would be forced to use a text field.
Again, I did not research this, just random thoughts on a possible solution.
Steve
-
Declare @t Table is a temporary object, and if declared within the Calling procedure, it cannot be passed to a nested procedure.
create procedure TestpasstableParamChild @TitleType varchar(50), @TTable table (pk int identity, Title varchar(50), pubID int, price money) OUTPUT
as
insert into @TTable
select title, pub_id, price
from titles
where type = @TitleType
GO
Response:
Server: Msg 156, Level 15, State 1, Procedure TestpasstableParamChild, Line 2
Incorrect syntax near the keyword 'table'.
Server: Msg 137, Level 15, State 1, Procedure TestpasstableParamChild, Line 6
Must declare the variable '@TTable'.
-
Sure you could do it this way, but if xml doc does exceed 8000 characters, then you are in trouble, because you cannot declare @variable text within a stored procedure.
Server: Msg 2739, Level 16, State 1, Line 0
The text, ntext, and image data types are invalid for local variables.
Just because we can make clones, doesn't mean we should.
-
Thanks for the clarification Ray.
Glad someone with a lot of expierence is watching over our sholder.
That is never a bad thing.
Steve
-
You can return a result set from a SP if the stored procedure consists of nothing more than the SELECT statement.
CREATE PROCEDURE ct_TableReturn (@var1 CHAR(8))
AS
SELECT col1, col2, col3 FROM Table1
WHERE col1 = @var1
GO
You can then insert these results into a temporary table
INSERT INTO #temptable (col1,col2,col3)
EXEC dbo.ct_TableReturn @var1='ABCDEFGH'
If it ain't broke, don't fix it...
-
Try this:
drop procedure dbo.ClntExport
go
create procedure dbo.ClntExport (@par1 varchar(50)) as
--do some stuff
select 'The parameter is: ' as MyDesc,@par1 as MyPar union
select 'Dummy record is : ','Dummy'
go
drop procedure dbo.ClntImport
go
create procedure dbo.ClntImport (@par1 varchar(50)) as
--do some stuff
create table #tmp1(MyDesc varchar(50),MyPar varchar(50))
INSERT INTO #tmp1 (MyDesc,MyPar)
EXEC dbo.ClntExport @par1
select * from #tmp1 for xml raw
go
--Your program:
EXEC dbo.ClntImport @par1 = 'This Works!'
-
I've passed temp tables between nested PROCs... but ya got to use global temp tables.. ##tbl_1 (two # signs required)
Proc_A
... do stuff..
EXEC Proc_B (which creates and populates ##Tbl_1)
Select * From ##Tbl_1
From BOL:
Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
SQL statements reference the temporary table using the value specified for table_name in the CREATE TABLE statement:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)INSERT INTO #MyTempTable VALUES (1)If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:
- A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
- All other local temporary tables are dropped automatically at the end of the current session.
- Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
A local temporary table created within a stored procedure or trigger can have the same name as a temporary table created before the stored procedure or trigger is called. However, if a query references a temporary table, and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against. Nested stored procedures can also create temporary tables with the same name as a temporary table created by the stored procedure that called it. All references to the table name in the nested stored procedure are resolved to the table created in the nested procedure, for example:
CREATE PROCEDURE Test2ASCREATE TABLE #t(x INT PRIMARY KEY)INSERT INTO #t VALUES (2)SELECT Test2Col = x FROM #tGOCREATE PROCEDURE Test1ASCREATE TABLE #t(x INT PRIMARY KEY)INSERT INTO #t VALUES (1)SELECT Test1Col = x FROM #tEXEC Test2GOCREATE TABLE #t(x INT PRIMARY KEY)INSERT INTO #t VALUES (99)GOEXEC Test1GOHere is the result set:
(1 row(s) affected)Test1Col ----------- 1 (1 row(s) affected)Test2Col ----------- 2When you create local or global temporary tables, the CREATE TABLE syntax supports constraint definitions with the exception of FOREIGN KEY constraints. If a FOREIGN KEY constraint is specified in a temporary table, the statement returns a warning message indicating that the constraint was skipped, and the table is still created without the FOREIGN KEY constraints. Temporary tables cannot be referenced in FOREIGN KEY constraints.
Consider using table variables instead of temporary tables. Temporary tables are useful in cases when indexes need to be created explicitly on them, or when the table values need to be visible across multiple stored procedures or functions. In general, table variables contribute to more efficient query processing. For more information, see table.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply