Click here to Skip to main content
15,901,205 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
I have created stored procedure to get tax name with amount when I execute query from window it gives proper result but While storing result of this into temp table it doesn't show proper result as well as there null row comes in result.
SQL
ALTER PROCEDURE spGetTotals
	-- Add the parameters for the stored procedure here
	@Id uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	Declare @SubTotal Decimal(10,2)
	SET @SubTotal = (SELECT SUM(Cost) From InvoiceItem WHERE InvoiceId =@Id)
	
	SET @SubTotal += (SELECT SUM(Charges) From Invoice_ExtraCharge WHERE InvoiceId=@Id)
	
	CREATE TABLE #TaxAmount
	(
		TaxName varchar(50),
		Charge Decimal(10,2)
	);
	
        --using stored procedure 
	INSERT INTO #TaxAmount
	EXEC spGetTaxAmountForTallyByInvoiceId @Id;
	
	select * from #TaxAmount
	DECLARE @Total As Decimal(12,2)
	SET @Total = @SubTotal-- + (SELECT SUM(Charge) FROM #TaxAmount)
	
	Select @SubTotal As SubTotal,@Total AS Total
END
GO
This my Query to use stored procedure. So I got 1st column null for both columns and result which I get in 2nd row in also wrong
Posted
Comments
Nayan Ambaliya 21-Oct-13 19:06pm    
You need to clarify what this line do:
EXEC spGetTaxAmountForTallyByInvoiceId @Id;

Also you need to tell what you mean by "2nd Row is also wrong"
Nayan Ambaliya 21-Oct-13 19:07pm    
You need to clarify what is inserted on the line:
EXEC spGetTaxAmountForTallyByInvoiceId @Id;

There might be something which is causing this OR because it is # temp tables, something previously entered might cause it since you are not deleting any entries
Rakshith Kumar 12-Nov-13 4:20am    
Could you provide me with the details of the table structure
j snooze 10-Jun-14 17:55pm    
Might want to make sure you don't have a temp table called #TaxAmount in the stored procedure you're calling(spGetTaxAmountForTallyByInvoiceId). temp tables can be accessed from different processes being run on the same connection.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900