SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Store proc returns value twice
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vijays3
Constraint Violating Yak Guru

India
338 Posts

Posted - 10/16/2013 :  07:00:40  Show Profile  Reply with Quote
Hi All

I have created proc which takes the parameters
like below

exec pr_Admin_AddTournamentPrizeFixedAmount_Bulk null,1,'500,10'

And below is the create script of that proc


CREATE PROCEDURE dbo.pr_Admin_AddTournamentPrizeFixedAmount_Bulk @OperatorID int, 
                                                                 @EnabledStatusID int, 
                                                                 @TournamentPrizeAmountsCSV nvarchar(MAX)
                                                                 
                                                                             
AS
BEGIN
  SET NOCOUNT ON
  
  DECLARE @ErrorString nvarchar(max)
  DECLARE @Min int, @Max int
  DECLARE @retVal int
  DECLARE @Amount int, @TournamentPrizeID int

  CREATE TABLE #PrizeIDAmount (TournamentPrizeID int, Amount int)
 
  IF EXISTS (SELECT * FROM tempdb.sys.tables WHERE name like'#PrizeAmount%')
  DROP TABLE #PrizeAmount
  CREATE TABLE #PrizeAmount(Id int identity (1,1),EnabledStatusID int, Amount int)
  
  INSERT INTO #PrizeAmount(EnabledStatusID, Amount)
  SELECT @EnabledStatusID AS EnabledStatusID ,
          value AS Amount 
  FROM [dbo].[fn_SplitDelimitedList] (@TournamentPrizeAmountsCSV ,',')

  SELECT @Min = MIN(Id), @Max = MAX(Id) FROM #PrizeAmount

  WHILE ( @Min <= @Max) 
  BEGIN 

     SELECT @Amount = Amount FROM #PrizeAmount WHERE Id = @Min
     
     Exec [dbo].[pr_Admin_AddTournamentPrizeFixedAmount]  @OperatorID = @OperatorID,
                                                          @EnabledStatusID = @EnabledStatusID,
                                                          @Amount = @Amount,
                                                          @TournamentPrizeID = @TournamentPrizeID OUTPUT
    
     INSERT into  #PrizeIDAmount (TournamentPrizeID, Amount) values (@Amount,@TournamentPrizeID)
    
   
   DELETE FROM #PrizeAmount WHERE Id = @Min
   SET @Min =@Min+1

   SELECT * from #PrizeIDAmount
    
  END 
  
  END 


Problem in this proc is it returns result twice

first result:
TournamentPrizeID Amount
500 10001

Second result:

TournamentPrizeID Amount
500 10001
10 10012


I guess There is a problem in while loop where I am
calling another proc and also inserting
records in temp table.But not sure How to fix this

I want only Second result

TournamentPrizeID Amount
500 10001
10 10012

Please suggest me how can I go with this

Vijay is here to learn something from you guys.

Edited by - vijays3 on 10/16/2013 07:03:18

vijays3
Constraint Violating Yak Guru

India
338 Posts

Posted - 10/16/2013 :  07:13:18  Show Profile  Reply with Quote
fixed this problem .It has small code issue..
I should have used Select statement at the bottom between END


END

SELECT * from #PrizeIDAmount

END


Vijay is here to learn something from you guys.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000