February 27, 2008 at 4:33 am
Hi all,
what should I say as subject line itself revealed my question. Scenario is like this, I have a sp (uspcalsalary) that will return total salary calculated on the basis of parameter. But it does not have any output parameter. And now, I want to get this calcualted amunt inside another SP (say, uspGetEmpDetail).
Please suggest me the way to do this.
Thanks in advance,
Amit.
February 27, 2008 at 4:41 am
Amit,
do you have control over the original stored procedure? I.e. can you rewrite it as a function (either scalar if it returns a single value or table returning function?)
Regards,
Andras
February 27, 2008 at 4:43 am
Yes Andras,
Function would be better here.
Cheers!
Sandy.
--
February 27, 2008 at 4:47 am
Alternatively, if you cannot rewrite it as a function, you can insert the result into a temporary table, like (without any error handling, ...)
CREATE PROC uspGetEmpDetail
AS
BEGIN
CREATE TABLE #f ( col1 INT )
INSERT #f
EXEC uspcalsalary
SELECT col1 AS dosomeprocessing FROM #f
DROP TABLE #f
END
This has quite a few disadvantages though.
Regards,
Andras
February 27, 2008 at 4:57 am
hey Andras,
I have a quick question for you,
If you are using more than one Stored procedure, then how can you handle the transaction process,
Ex: -
SP -1
(
Begin Tran
-- codes
-- calling SP - 2
Commit
)
SP -2
(
Begin Tran
-- codes
Commit
)
I mean to say can you get control over your transaction????
If yes, Then How?
If No, They How can we achieve this???
Cheers!
Sandy.
--
February 27, 2008 at 5:08 am
Hi Andras,
Thanks for your reply.
But can I store the result in a variable.
Thanks.
Amit
February 27, 2008 at 7:16 am
amit (2/27/2008)
Hi Andras,Thanks for your reply.
But can I store the result in a variable.
Thanks.
Amit
You could use a table variable and then get the result out of it like
CREATE PROC uspGetEmpDetail
AS
BEGIN
DECLARE @f TABLE ( col1 INT )
INSERT @f
EXEC uspcalsalary
DECLARE @variable INT
SELECT TOP 1
@variable = col1
FROM @f
SELECT @variable
END
But this is once again a hack and is not a good replacement for functions 🙂
Andras
February 27, 2008 at 7:22 am
Sandy (2/27/2008)
hey Andras,I have a quick question for you,
If you are using more than one Stored procedure, then how can you handle the transaction process,
Ex: -
SP -1
(
Begin Tran
-- codes
-- calling SP - 2
Commit
)
SP -2
(
Begin Tran
-- codes
Commit
)
I mean to say can you get control over your transaction????
If yes, Then How?
If No, They How can we achieve this???
Cheers!
Sandy.
Hi Sandy,
when you begin a new transaction inside a transaction (your original stored procedure may as well have been inside a transaction), nothing really happens apart from the transaction depth counter gets incremented. You can query this by looking at @@TRANCOUNT
When you commit a "nested" transaction, nothing gets committed, only the counter gets decremented. When you commit the outermost transaction, the transaction commits.
However, when you roll back, no matter how many begin transactions you have, the main transaction will be rolled back. This is described in more detail in Books Online under http://msdn2.microsoft.com/en-us/library/ms189336.aspx
Regards,
Andras
February 27, 2008 at 7:38 am
Andras Belokosztolszki (2/27/2008)
When you commit the outermost transaction, the transaction commits.However, when you roll back, no matter how many begin transactions you have, the main transaction will be rolled back.
...along with all of the "nested ones" , even if they've supposedly hit their commit stage.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 27, 2008 at 11:14 pm
Thanks Andras,
http://msdn2.microsoft.com/en-us/library/ms189336.aspx
Above URL is really helpful for me,
Cheers!
Sandy.
--
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply