Execute stored procedure from stored procedure
Hello,
i need to execute a stored procedure from another stored procedure.
For example:
StoredProcedure1: Returns a table with columns "year" and "value".
StoredProcedure2: Also returns a table with columns "year" and "value".
In Stored Procedure3: I would like to use the results from StoredProcedure1 and StoredProcedure2 and join them.
Can somebody give me an example how to execute a stored procedure from another one and use the result table for a new command?
Kind regards and thanks,
carsaw
|
0 |
|
carsaw
10/30/2006 9:28:42 AM
asp.net.sql-datasource
29906 articles. 
0 followers.
2 Replies
1035 Views
hi,
first off all let me clarify here - you cannot return table from a stored procedure you can use output variables but not the table.
what i would suggest you from your qeustion is - sp3 is main sp for you which needs data from sp1 & sp2 right
in that case let sp3 contain a temporary table #temptable with required columns
--parent proc
alter proc p1
as
Create table #tempu
(
pid int identity(1,1),
names varchar(50)
)
begin
insert #tempu(names)
exec tempinserter
select * from #tempu
end
--2nd proc
alter proc tempinserter
as
Create table #temp2
(
pid int identity(1,1),
names varchar(50)
)
insert #temp2
values('satish')
select names from #temp2
return
--similarly create 3rd one then
exec p1 --the parent proc
hope it helps
also keep in mind to drop #temp tables at end of execution
regards,
satish.
Kind Attn: If a reply to your post helped you, kindly mark it as Answered.
__________________________________________________
Please save Animals Help World Society For Protection Of Animals,
Protect these speechless creatures of GOD
|
|
0 |
|
satish_nagdev
10/30/2006 1:36:41 PM
like i've called tempinserter in parent proc you can call other procs in the same manner.
regards,
satish
Kind Attn: If a reply to your post helped you, kindly mark it as Answered.
__________________________________________________
Please save Animals Help World Society For Protection Of Animals,
Protect these speechless creatures of GOD
|
|
0 |
|
satish_nagdev
10/30/2006 1:47:16 PM
Similar Artilces:
Using ADO.NET Execute Second Stored Procedure Depending of Output of First Stored Procedure
When a user logs onto the app, I capture their UserId, today's date and thier WINNT using ADO.NET calling a stored procedue-no problem. Now, once the above mention fields have been populated, when the same user logs on again and this time click their daily attendance time, I need to execute and update stored procedure-no problem. Each time the user logs onto the attendance page, they need to be able to view the previuos time stamps, on page load.- no problem The problem is that I cannot get all three to work in concert, although they will work independently, somewhat. Sotred proc...
SQL Stored Procedure to MySQL Stored Procedure Conversion
Hi, I am trying to convert a stored procedure written for sql to one that will work in mysql. I understand that I have to set the variables as IN , but I don't know what to do with the rest of the code. The following is the sql stored procedure that I am trying to convert to msql stored procedure: CREATE PROCEDURE Register_User (@userName Varchar(50), (@PassWord Varchar(50), (@FirstName Varchar(50), (@LastName Varchar(50) ) AS IF EXISTS(SELECT u_ID FROM User_ID Where u_UserName=@UserName) RETURN -1 ELSE INSERT User_ID( u_User...
SQL Stored Procedure Issue
This is the Stored Procedure below -> SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO /****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/3/2007 4:50:23 PM ******/ /****** Object: Stored Proc...
Calling a stored procedure inside another stored procedure (or "nested stored procedures")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to&n...
sql count using stored procedure withing stored procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like thisSelect Sum(dbo.CalculateHours(User...
How to convert Sql Server Stored Procedures into Oracle Stored Procedures
Hi All, I am migrating from sql server2000 to oracle. I have moved all table into oracle manually. Now I need to move stored procedure. I don't know how to convert sql server stored procedure into oracle stored procedure form. Is there any tool which will convert directly. Please some one help me. Thank you. Hi there,Please use the tool bellow, which does the job you want. http://www.swissql.com/products/sqlserver-to-oracle/sql-server-to-oracle.html thanks sharp guy...
MS SQL stored procedures inside another stored procedure
Hi, Do you know how to write stored procedures inside another stored procedure in MS SQL. Create procedure spMyProc inputData varchar(50) AS ----- some logical procedure spMyProc inputInsideData varchar(10) AS --- some logical --- go ------- What exactly are tou trying to do? ***********************Dinakar NethiLife is short. Enjoy it.*********************** Like Function, you can have one function inside another another function. Function1 () { Function2() } How about store procedure ? spProc1 { &n...
calling a stored procedure from a stored procedure
Hi Group! I'm on ASA 8.02 Build 4411 writing sprocs in WATCOM SQL. I have a sproc which breaks a given string-parameter into pieces. It's result set contains the pieces, e.g. last line of sproc: "select item from mytable;". This sproc works fine. Now I need to call this procedure from another procedure which has a variable result set. I tried the following: <snip... set ll_retval = 0; call sp_breakstring(as_bzp,','); BreakLoop: while ll_retval <> 100 and not ll_retval < 0 loop set li_zaehler = li_zaehler + 1; fetch next sp_breakstrin...
Calling a Stored Procedure from a Stored Procedure
Can you do this? I have a sp that returns a list of eligible accounts and then I use that list in other sp but I don't want to keep copying the code, I would like to be able to use it as a sub query in other sp but I don't know if this is possible or what the syntax would be. Thanks, Aaron You cannot do that directly. You can: 1. Use User defined functions which can return a resultset 2. Use a local #temp table which can hold the resultset of the called stored procedure and use this #temp table in your subsequent processing INSERT #tempTable EXEC s...
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...
Call as stored procedure from another stored procedure
I am using a stored procedure (SP_A) to insert rows to a table. This is done by the cursor within the procedure. Value for one of the column is provided by SP_B. My problem is that when i call SP_B in SP_A it does not return any value (rather i do not know how to capture it from SP_A). If executed individually it returns a value. Please help with example if something specific needs to be taken care. Suresh wrote: > > Please help with example if something specific needs to be > taken care. Please always post the version and build number of ASA that you are using (e....
Calling a stored procedure from another stored procedure
Hello, I am now using Interbase again for a project and I am trying to do this: begin /* Procedure Text */ /* Steps: - Verify if protocol exists and fetch protocol id - add activity - add document - add activity protocol reeturn document id */ select id from protocols where protocols.protname = :protocol into :protid; /* protocol exists? Gooooood! */ if ( :protid is not null ) then begin execute addactivity(:userid,'New document') returning_values :actid; execute adddocument( :docname,:docpath...
how to call stored procedure from another stored procedure?
is it possible to call a stored procedure from another stored procedure?-keeara g------------------ Inside your first Stored Procedure: exec <name of SP to run> <Eventual Parameters this SP requires> Here is a link to all you want to know about executing SPs from other SPs. You can even Execute SPs on other SQL Servers.RegardsAndre Colbiornsen ---------------------------------Seventh DayRåbygatan 1A,SE-223 61 LundSwedenMob.: +46-(0)708-97 78 79Mail: info@seventhday.se--------------------------------...
Calling stored procedure from another stored procedure
Is it possible to call one sp from another sp?I've been hunting around for an example to do this and just can't seem to find one.Anyone have a link for this or a sample?Thanks all,Zath Yes, you can. Just use EXEC usp_secondStoredProc @params inside your first SP.Nick...
When a user logs onto the app, I capture their UserId, today's date and thier WINNT using ADO.NET calling a stored procedue-no problem. Now, once the above mention fields have been populated, when the same user logs on again and this time click their daily attendance time, I need to execute and update stored procedure-no problem. Each time the user logs onto the attendance page, they need to be able to view the previuos time stamps, on page load.- no problem The problem is that I cannot get all three to work in concert, although they will work independently, somewhat. Sotred proc...
SQL Stored Procedure to MySQL Stored Procedure Conversion
Hi, I am trying to convert a stored procedure written for sql to one that will work in mysql. I understand that I have to set the variables as IN , but I don't know what to do with the rest of the code. The following is the sql stored procedure that I am trying to convert to msql stored procedure: CREATE PROCEDURE Register_User (@userName Varchar(50), (@PassWord Varchar(50), (@FirstName Varchar(50), (@LastName Varchar(50) ) AS IF EXISTS(SELECT u_ID FROM User_ID Where u_UserName=@UserName) RETURN -1 ELSE INSERT User_ID( u_User...
SQL Stored Procedure Issue
This is the Stored Procedure below -> SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO /****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/3/2007 4:50:23 PM ******/ /****** Object: Stored Proc...
Calling a stored procedure inside another stored procedure (or "nested stored procedures")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to&n...
sql count using stored procedure withing stored procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like thisSelect Sum(dbo.CalculateHours(User...
How to convert Sql Server Stored Procedures into Oracle Stored Procedures
Hi All, I am migrating from sql server2000 to oracle. I have moved all table into oracle manually. Now I need to move stored procedure. I don't know how to convert sql server stored procedure into oracle stored procedure form. Is there any tool which will convert directly. Please some one help me. Thank you. Hi there,Please use the tool bellow, which does the job you want. http://www.swissql.com/products/sqlserver-to-oracle/sql-server-to-oracle.html thanks sharp guy...
MS SQL stored procedures inside another stored procedure
Hi, Do you know how to write stored procedures inside another stored procedure in MS SQL. Create procedure spMyProc inputData varchar(50) AS ----- some logical procedure spMyProc inputInsideData varchar(10) AS --- some logical --- go ------- What exactly are tou trying to do? ***********************Dinakar NethiLife is short. Enjoy it.*********************** Like Function, you can have one function inside another another function. Function1 () { Function2() } How about store procedure ? spProc1 { &n...
calling a stored procedure from a stored procedure
Hi Group! I'm on ASA 8.02 Build 4411 writing sprocs in WATCOM SQL. I have a sproc which breaks a given string-parameter into pieces. It's result set contains the pieces, e.g. last line of sproc: "select item from mytable;". This sproc works fine. Now I need to call this procedure from another procedure which has a variable result set. I tried the following: <snip... set ll_retval = 0; call sp_breakstring(as_bzp,','); BreakLoop: while ll_retval <> 100 and not ll_retval < 0 loop set li_zaehler = li_zaehler + 1; fetch next sp_breakstrin...
Calling a Stored Procedure from a Stored Procedure
Can you do this? I have a sp that returns a list of eligible accounts and then I use that list in other sp but I don't want to keep copying the code, I would like to be able to use it as a sub query in other sp but I don't know if this is possible or what the syntax would be. Thanks, Aaron You cannot do that directly. You can: 1. Use User defined functions which can return a resultset 2. Use a local #temp table which can hold the resultset of the called stored procedure and use this #temp table in your subsequent processing INSERT #tempTable EXEC s...
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...
Call as stored procedure from another stored procedure
I am using a stored procedure (SP_A) to insert rows to a table. This is done by the cursor within the procedure. Value for one of the column is provided by SP_B. My problem is that when i call SP_B in SP_A it does not return any value (rather i do not know how to capture it from SP_A). If executed individually it returns a value. Please help with example if something specific needs to be taken care. Suresh wrote: > > Please help with example if something specific needs to be > taken care. Please always post the version and build number of ASA that you are using (e....
Calling a stored procedure from another stored procedure
Hello, I am now using Interbase again for a project and I am trying to do this: begin /* Procedure Text */ /* Steps: - Verify if protocol exists and fetch protocol id - add activity - add document - add activity protocol reeturn document id */ select id from protocols where protocols.protname = :protocol into :protid; /* protocol exists? Gooooood! */ if ( :protid is not null ) then begin execute addactivity(:userid,'New document') returning_values :actid; execute adddocument( :docname,:docpath...
how to call stored procedure from another stored procedure?
is it possible to call a stored procedure from another stored procedure?-keeara g------------------ Inside your first Stored Procedure: exec <name of SP to run> <Eventual Parameters this SP requires> Here is a link to all you want to know about executing SPs from other SPs. You can even Execute SPs on other SQL Servers.RegardsAndre Colbiornsen ---------------------------------Seventh DayRåbygatan 1A,SE-223 61 LundSwedenMob.: +46-(0)708-97 78 79Mail: info@seventhday.se--------------------------------...
Calling stored procedure from another stored procedure
Is it possible to call one sp from another sp?I've been hunting around for an example to do this and just can't seem to find one.Anyone have a link for this or a sample?Thanks all,Zath Yes, you can. Just use EXEC usp_secondStoredProc @params inside your first SP.Nick...
Web resources about - Execute stored procedure from stored procedure - asp.net.sql-datasource
Invasiveness of surgical procedures - Wikipedia, the free encyclopediaThere are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...
Procedure is more a snap than a snipA QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...
Jetstar procedures under investigation after planes took off too heavyJetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...
Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...
NFL security procedures in question after rappelling protesters
Two protesters with sophisticated gear disrupted the Panthers-Colts game in Charlotte Monday night, bringing into question security at the stadium ...
Two protesters with sophisticated gear disrupted the Panthers-Colts game in Charlotte Monday night, bringing into question security at the stadium ...
'Painless' dental cavity procedure regrows tooth enamel... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...
Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'
Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'
Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'
Robust Growth In Procedures, Beneficial Product Mix Drive Intuitive Surgical's Strong Q3 Performance
The company’s non-GAAP gross margin improved by over 2 percentage points year-on-year due to higher average selling prices, cost savings, and ...
The company’s non-GAAP gross margin improved by over 2 percentage points year-on-year due to higher average selling prices, cost savings, and ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners
Concession: Ryan promises Freedom Caucus he’ll delay discussion of reforming procedure for removing the ...Compromise. Like I said this morning, it sounds like they did a little horse-trading at their summit. Ryan wants them to give up their right ...
Resources last updated: 12/8/2015 3:12:55 PM