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 this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:
SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')
INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')
INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
|
0 |
|
nuge
11/1/2007 11:55:45 AM
asp.net.sql-datasource
29906 articles. 
0 followers.
1 Replies
1500 Views
Two steps:
1. Create the SP to return new ID like:
CREATE PROCEDURE [dbo].[ deriveStatusID] @Created Varchar(100),@statusID int OUTPUT
AS
SELECT @statusID = statusID FROM statusList WHERE statusName=@Created
2. Modify your second SP as:
.....
Declare @NewStatusId Int
EXEC deriveStatusID ‘Created', @NewStatusId OUTPUT
INSERT INTO Products (productname, statusID) VALUES (''widget1', @NewStatusId )
"Object reference not set to an instance of an object"
|
|
0 |
|
che3358
11/1/2007 1:39:45 PM
Similar Artilces:
"Action Stored Procedure" vs "Stored Procedure"
How would you explain an "Action Stored Procedure" as opposed to just "Stored Procedure"RegardsAndre Colbiornsen ---------------------------------Seventh DayRåbygatan 1A,SE-223 61 LundSwedenMob.: +46-(0)708-97 78 79Mail: info@seventhday.se-------------------------------- I would say that an "action stored procedure" is one which returns data records back to the calling program. A regular "stored procedure" is one which only returns OUTPUT parameters or a RETURN value to the calling program. I personally do not distinguish between the two and call...
calling a store procedure inside another stored procedure
CREATE PROCEDURE `sp_test`.`proc_tot_record`(FK_sites_COL INT ) BEGIN SELECT COUNT(*) FROM tbl_virtual_drive WHERE tbl_virtual_drive.FK_sites = FK_sites_COL; END CREATE PROCEDURE `test`.`paging_with_param`(FK_Sites_COL INT, Start_Limit_COL INT, End_Limit_COL INT) BEGINPREPARE STMT FROM "SELECTtbl_virtual_drive.PK_virtual_drive,tbl_virtual_drive.file_name,tbl_virtual_drive.file_extension,tbl_virtual_drive.FK_sitesFROMtbl_virtual_driveWHEREtbl_virtual_drive.FK_sites = ?ORDER BYtbl_virtual_drive.file_nameLIMIT ?,?";EXECUTE STMT USING @FK_Sites, @Start_Limit, @End_Limit...
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...
call one stored procedure from inside another stored procedure
How do you call one stored procedure from inside another stored procedure and return a value?Does this hinder performance?Which is better, do it as above, or have a .NET method calling the stored procedure A, getting the value it returns and then passing it to stored procedure B? You can use "EXEC your stored procedure name" in a stored procedure to call another stored procedure and this way will be faster than call A and then B from web app.This posting is provided "AS IS" with no warranties, and confers no rights. Cheers JimmyM, thanks for that. How can I pass parameters from o...
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 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...
Call stored procedure from another stored procedure?
How do I call this stored procedure: CREATE Procedure hentSpecialPris ( @varenr int, @cvr int, @pris decimal(7,2) OUTPUT ) AS DECLARE @antal int SET @antal = 0 SET @antal = (SELECT COUNT(*) FROM rabat WHERE varenr=@varenr and cvr = @cvr) IF @antal < 1 BEGIN SET @pris = (SELECT pris FROM vare WHERE varenr=@varenr) END ELSE BEGIN SET @pris = (SELECT pris FROM rabat WHERE varenr=@varenr and cvr = @cvr) END GO from another stored procedure?? I want to get the output value of the stored procedure? ...
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 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...
calling another stored procedure within a stored procedure?
Hi, I have a while loop stored procedure, I need to send email for each item in the loop using a sendemail stored procedure. I have two question .. 1) I use EXEC PRODUCTION.DBO.SENDMAIL 'email@hotmail.com', 'Start', 'Job Start' in side the while loop stored procedure, but i didn't get any email or error msg. Why? 2) I try to move a file .. how do I find out if the moving is completed successfully? -- MOVE FILES SET @CMD = 'MOVE /Y ' + '"' + @ORIGINAL_FILE + '"' + ' "' + @MOVE_FILE + '"' EXEC master.dbo.xp_cmdshell @CMD Thanks for your reply in advance. 1. I hav...
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...
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...
Is there a utility to see which stored procedures call another stored procedure?
I'm looking for a dependency diagram / pretty print utility for stored procedures / functions in SQL Anywhere. Thank You in advance I don't know of any pretty print utility, but the built in function traceback may be of help to you. Jason Hinsperger International and Sustaining Engineering Adaptive Server Anywhere Chris McCormack wrote: > > I'm looking for a dependency diagram / pretty print utility for stored > procedures / functions in SQL Anywhere. > > Thank You in advance ...
("Could not find stored procedure ''.") when calling a user defined procedure
Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn...
How would you explain an "Action Stored Procedure" as opposed to just "Stored Procedure"RegardsAndre Colbiornsen ---------------------------------Seventh DayRåbygatan 1A,SE-223 61 LundSwedenMob.: +46-(0)708-97 78 79Mail: info@seventhday.se-------------------------------- I would say that an "action stored procedure" is one which returns data records back to the calling program. A regular "stored procedure" is one which only returns OUTPUT parameters or a RETURN value to the calling program. I personally do not distinguish between the two and call...
calling a store procedure inside another stored procedure
CREATE PROCEDURE `sp_test`.`proc_tot_record`(FK_sites_COL INT ) BEGIN SELECT COUNT(*) FROM tbl_virtual_drive WHERE tbl_virtual_drive.FK_sites = FK_sites_COL; END CREATE PROCEDURE `test`.`paging_with_param`(FK_Sites_COL INT, Start_Limit_COL INT, End_Limit_COL INT) BEGINPREPARE STMT FROM "SELECTtbl_virtual_drive.PK_virtual_drive,tbl_virtual_drive.file_name,tbl_virtual_drive.file_extension,tbl_virtual_drive.FK_sitesFROMtbl_virtual_driveWHEREtbl_virtual_drive.FK_sites = ?ORDER BYtbl_virtual_drive.file_nameLIMIT ?,?";EXECUTE STMT USING @FK_Sites, @Start_Limit, @End_Limit...
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...
call one stored procedure from inside another stored procedure
How do you call one stored procedure from inside another stored procedure and return a value?Does this hinder performance?Which is better, do it as above, or have a .NET method calling the stored procedure A, getting the value it returns and then passing it to stored procedure B? You can use "EXEC your stored procedure name" in a stored procedure to call another stored procedure and this way will be faster than call A and then B from web app.This posting is provided "AS IS" with no warranties, and confers no rights. Cheers JimmyM, thanks for that. How can I pass parameters from o...
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 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...
Call stored procedure from another stored procedure?
How do I call this stored procedure: CREATE Procedure hentSpecialPris ( @varenr int, @cvr int, @pris decimal(7,2) OUTPUT ) AS DECLARE @antal int SET @antal = 0 SET @antal = (SELECT COUNT(*) FROM rabat WHERE varenr=@varenr and cvr = @cvr) IF @antal < 1 BEGIN SET @pris = (SELECT pris FROM vare WHERE varenr=@varenr) END ELSE BEGIN SET @pris = (SELECT pris FROM rabat WHERE varenr=@varenr and cvr = @cvr) END GO from another stored procedure?? I want to get the output value of the stored procedure? ...
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 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...
calling another stored procedure within a stored procedure?
Hi, I have a while loop stored procedure, I need to send email for each item in the loop using a sendemail stored procedure. I have two question .. 1) I use EXEC PRODUCTION.DBO.SENDMAIL 'email@hotmail.com', 'Start', 'Job Start' in side the while loop stored procedure, but i didn't get any email or error msg. Why? 2) I try to move a file .. how do I find out if the moving is completed successfully? -- MOVE FILES SET @CMD = 'MOVE /Y ' + '"' + @ORIGINAL_FILE + '"' + ' "' + @MOVE_FILE + '"' EXEC master.dbo.xp_cmdshell @CMD Thanks for your reply in advance. 1. I hav...
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...
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...
Is there a utility to see which stored procedures call another stored procedure?
I'm looking for a dependency diagram / pretty print utility for stored procedures / functions in SQL Anywhere. Thank You in advance I don't know of any pretty print utility, but the built in function traceback may be of help to you. Jason Hinsperger International and Sustaining Engineering Adaptive Server Anywhere Chris McCormack wrote: > > I'm looking for a dependency diagram / pretty print utility for stored > procedures / functions in SQL Anywhere. > > Thank You in advance ...
("Could not find stored procedure ''.") when calling a user defined procedure
Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn...
Web resources about - Calling a stored procedure inside another stored procedure (or "nested stored procedures") - asp.net.sql-datasourceResources last updated: 12/19/2015 4:26:17 PM