March 10, 2008 at 1:56 am
Hi,
I would like to use a stored procedure that calls other stored procedures and use a
temporary table to pass the results back and forth.(from Parent to child)
anybody help me doing it....
March 10, 2008 at 3:18 am
guptaopus (3/10/2008)
Hi,I would like to use a stored procedure that calls other stored procedures and use a
temporary table to pass the results back and forth.(from Parent to child)
anybody help me doing it....
You can create a temptable in a stored proc, and use the name of this temp table in a stored procedure that you call. E.g.:
CREATE PROC p1
AS
SELECT *
FROM #foo
go
CREATE TABLE #foo ( a INT )
go
EXEC p1
BUT, note that the procedure that references the temp table which is not created inside it will be recompiled every time it is executed. (if the procedures are small/not executed very frequently, this will not be an issue).
On SQL Server 2008 you will be able to pass a table variable as a parameter, but SS2k8 is not yet released 🙂
An alternative is to use a fixed (non temp) table, and segregate concurrent procedures using a token or some special value in an extra field.
Maybe if you describe your problem in more detail we can suggest a better solution 🙂
Andras
Regards,
Andras
March 10, 2008 at 4:03 am
I can see it coming... RBAR on steroids... someone's going to make a temp table in the "outer" procedure and then step through it a row or two at a time in the inner procedures.
--Jeff Moden
March 10, 2008 at 4:27 am
Hi,
My senerio is...
CREATE PROC p1
AS
CREATE TABLE #foo ( a INT )
EXEC p2
go
CREATE PROC p2
AS
SELECT *
FROM #foo
go
When i tried executing it it throws an error msg:
table #foo dosen't exist.
May i know wat is the reason?
March 10, 2008 at 4:31 am
guptaopus (3/10/2008)
Hi,My senerio is...
CREATE PROC p1
AS
CREATE TABLE #foo ( a INT )
EXEC p2
go
CREATE PROC p2
AS
SELECT *
FROM #foo
go
When i tried executing it it throws an error msg:
table #foo dosen't exist.
May i know wat is the reason?
I assume you are trying to execute p1 (for me it works). If you try to execute p2, then of course you need to create the temp table first.
Regards,
Andras
March 10, 2008 at 4:33 am
BTW, Jeff explicitly mentioned the same worries I have about using temp tables in the above way. Would you mind telling us what you are trying to do? There may be a better solution 🙂
Regards,
Andras
March 10, 2008 at 4:51 am
There is one part of code which is common to all procedures(SP's)
I want to saperate that code as new SP and call if from respective procedures. below given is the sample code of which i want to call from other procedure.assume SP2.
================
Here I want to get value from SP1 to #Result table. here SP1 is calling SP2
CREATE PROCEDURE SP2
AS
BEGIN
IF EXISTS(SELECT ODSValidationID
FROM BV_DQ_ODSValidation V,
BV_DQ_odstable T,
BV_DQ_LoadInstance L
WHERE V.ODSTableID=T.ODSTableID
AND T.LoadInstanceID=L.LoadInstanceID
AND L.LoadInstanceID=@LoadInstanceID
AND JobID =@JobID
)
BEGIN
--Delete this job Data From ODSValidation
DELETE FROM BV_DQ_ODSValidation
WHERE ODSValidationID IN (SELECT ODSValidationID
FROM BV_DQ_ODSValidation V,
BV_DQ_odstable T,
BV_DQ_LoadInstance L
WHERE V.ODSTableID=T.ODSTableID
AND T.LoadInstanceID=L.LoadInstanceID
AND L.LoadInstanceID=@LoadInstanceID
AND JobID =@JobID)
END
BEGIN
--Insert the New Data into ODSValidation from #Result
INSERT INTO V_DQ_ODSValidation(JobID ,ODSTableID ,
ObjectExistingReferencePrimaryKey,
ObjectFieldName,
ObjectFieldValue,
ObjectReferenceName,
ObjectReferenceExtendedInfo ,
DataTypeID,
ResultID)
SELECT JobID ,ODSTableID,
ObjectExistingReferencePrimaryKey,
ObjectFieldName,
ObjectFieldValue,
ObjectReferenceName,
ObjectReferencedExtendedinfo,
DataTypeID,
ResultID
FROM #Result
WHERE JobID IS NOT NULL
AND ODSTableID IS NOT NULL
AND DataTypeID IS NOT NULL
AND ResultID IS NOT NULL
END
END
=============
March 10, 2008 at 5:02 am
I see where you are going with sp2, but what are you doing in sp1? Would it be possible to create sp1 as a view instead? Are there multiple steps happening in sp1 as in sp2?
March 10, 2008 at 5:13 am
Now below given is SP1 code in last line its calling SP2
================================
CREATE PROCEDURE ODS_VLD_JCU07
AS
SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @JobID INT,@LoadInstanceID INT, @ODSTableID INT,@DataTypeID SMALLINT,
@InvalidCharacterSet SMALLINT,
@ADDRTYPEDataTypeID SMALLINT,
@NamaDDR1DataTypeID SMALLINT,
@NamaDDR2DataTypeID SMALLINT,
@NamaDDR3DataTypeID SMALLINT,
@NamaDDR4DataTypeID SMALLINT,
@WORKADDR1DataTypeID SMALLINT,
@WORKADDR2DataTypeID SMALLINT,
@WORKADDR3DataTypeID SMALLINT,
@WORKADDR4DataTypeID SMALLINT,
@CharacterRange nvarchar(11)
SET @InvalidCharacterSet=10
SET @CharacterRange=N'%[?-?,?-?]%'
--Check That ODS_VLD_JC07 Is There or Not
--Passing Input-JobName,JobDescription,TableName
--OUTPUT-LoadInstanceID,JobID,ODSTableID
EXEC ODS_VLD_Header 'ODQ_VLD_JCU07','Retail Customer Arabic Address Validation Job','NBKODSDB.dbo.BV_RETDCUST_CUSTADDR',
@LoadInstanceID output,@JobID output,@ODSTableID output
--Check Whether NBKODSDB.dbo.BV_RETDCUST_CUSTADDR is there or not in schema
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'NBKODSDB.dbo.BV_RETDCUST_CUSTADDR' AND type = 'V')
BEGIN
CREATE TABLE #Result( JobID INT,ODSTableID INT,
ObjectExistingReferencePrimaryKey VARCHAR(40),
ObjectFieldName VARCHAR(256),
ObjectFieldValue NVARCHAR(256),
ObjectReferenceName VARCHAR(256),
ObjectReferenceExtendedInfo VARCHAR(256),
DataTypeID SMALLINT,
ResultID SMALLINT)
-- 1. Customer ADDRTYPE Validation
--Get DataType id
EXECUTE ODSValidationCommon_InsertObjectDataType 'NBKODSDB.dbo.BV_RETDCUST_CUSTADDR','ADDRTYPE',@DataTypeID OUTPUT
SET @ADDRTYPEDataTypeID=@DataTypeID
INSERT INTO #Result( JobID ,ODSTableID ,
ObjectExistingReferencePrimaryKey,
ObjectFieldName ,
ObjectFieldValue,
ObjectReferenceName ,
ObjectReferenceExtendedInfo ,
DataTypeID ,
ResultID )
SELECT @JobID AS JobID ,@ODSTableID AS ODSTableID ,
CUSNO AS ObjectExistingReferencePrimaryKey ,
'ADDRTYPE' AS ObjectFieldName ,
ADDRTYPE AS ObjectFieldValue,
'"' AS ObjectReferenceName,
'"' AS ObjectReferenceExtendedInfo,
@ADDRTYPEDataTypeID AS DataTypeID,
@InvalidCharacterSet AS ResultID
FROM NBKODSDB.dbo.BV_RETDCUST_CUSTADDR
WHERE
ADDRTYPE ='MA' AND ADDRTYPE IS NOT NULL
Exec SP2 @LoadInstanceID,@JobID
March 10, 2008 at 6:21 am
Well? Does it do what you want, now?
You explained what you're doing but not why... I'm still worried that you're building RBAR. If it's for a GUI, probably ok... if it's not, then not good and it will bite you in the future.
--Jeff Moden
March 10, 2008 at 6:26 am
Personally I would create #return as a real table.
For each instance of sp1 you are running
EXEC ODS_VLD_Header 'ODQ_VLD_JCU07','Retail Customer Arabic Address Validation Job','NBKODSDB.dbo.BV_RETDCUST_CUSTADDR',
@LoadInstanceID output,@JobID output,@ODSTableID output.
If you return the ID values from the procedure to the calling procedure you would have keys available to use to find the correct resultset. Then clear that resultset from the calling procedure when complete.
March 11, 2008 at 6:09 am
Andras: Re:
"On SQL Server 2008 you will be able to pass a table variable as a parameter, but SS2k8 is not yet released "
I wonder if this will allow an Excel external data query (MS Query) to pass a range?
March 11, 2008 at 6:13 am
Jim Russell (3/11/2008)
Andras: Re:"On SQL Server 2008 you will be able to pass a table variable as a parameter, but SS2k8 is not yet released "
I wonder if this will allow an Excel external data query (MS Query) to pass a range?
Not sure. There are some restrictions. You need to use a user defined table type (this must exist) before the procs are created. It does work with ADO (you pass in the table as a proc parameter), but I'm not sure if it can be made to work with Excel.
February 22, 2009 at 1:54 am
1) you are using temp table concept in sql.
2) both store procedures are run differently.
so it will tell you that temp table not present , it may be becouse of temp table after store procedure exec sql drop that table.
i think you try for global temp table or permenent table and after your work you progr. drop that table.
thank you
February 22, 2009 at 2:27 pm
[font="Verdana"]My suggestion would be to use a permanent working table, rather than a temporary table you create on the fly. From a design perspective, the working table will form part of the stored procedure interface. Whereas if you are calling the second stored procedure and don't have the permanent working table, you will have to go in and read the code to work out what format it needs.
You can easily make a working table useable concurrently, and have the second stored procedure clean out the data once it's finished with it.
[/font]
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply