Facing problem when calling store procedure from c# code

Mou_kol

I have store procedure which insert and update data and at the end it return multiple data. i am using SQL helper utility class to call store procedure. i use

ds = await SqlHelper.ExecuteDatasetAsync(ConnectionManager.GetConnectionString(), CommandType.StoredProcedure, "USP_InsertBMAsLineItem", spParameter);

execution stuck at this line. i am not able to figure out where i made the mistake. when i execute SP from SSMS then everything goes fine. please see my store procedure and c# code. tell me where i made the mistake.

ALTER PROC USP_InsertBMAsLineItem
(
@TickerID VARCHAR(20),
@CommaSeparatedItems VARCHAR(MAX),
@UserID VARCHAR(20)
)
AS
BEGIN
Declare @Start INT, @Count INT,@MaxOrder INT
SET @Start=1
SET @Count=1
SET @MaxOrder=0

 BEGIN TRY
 BEGIN TRAN
 DROP TABLE IF EXISTS #Tmpdata

 CREATE TABLE #Tmpdata
 (
     ID  INT Identity,
     LineItem VARCHAR(MAX)
 )

 INSERT INTO #Tmpdata(LineItem) 
 (
     SELECT value as LineItem
     FROM STRING\_SPLIT(@CommaSeparatedItems, ',')
 ) 

    

 MERGE INTO TblLineItemTemplate Trg
 USING
 (
     SELECT value as LineItem
     FROM STRING\_SPLIT(@CommaSeparatedItems, ',')
 ) AS Src
 ON UPPER(TRIM(Trg.LineItem)) = UPPER(TRIM(Src.LineItem))
    AND Trg.TickerID = @TickerID
 WHEN MATCHED THEN
     UPDATE SET Trg.Action = 'U', ModifiedBy=@UserID
 WHEN NOT MATCHED THEN
     INSERT
     (
         TickerID,
         LineItem,
         Action,
         InsertedOn,
         InsertedBy
     )
     VALUES
     (TRIM(@TickerID), TRIM(Src.LineItem), 'I', GETDATE(),@UserID);

    
 SELECT @Start=MIN(ID) FROM #Tmpdata
 SELECT @Count=MAX(ID) FROM #Tmpdata

 WHILE (@Start<=@Count)
 BEGIN
     IF NOT EXISTS(SELECT \* FROM tblSectionLineItemTemplate WHERE TickerID=@TickerID
         AND SectionID IN (SELECT SectionID FROM tblSectionTemplate WHERE TickerID=@TickerID AND Section='Model Output' AND Action<>'D')
         AND LineItemID IN (SELECT LineItemId FROM TblLineItemTemplate WHERE TickerID=@TickerID 
         AND LineItem IN (
                 SELECT LineItem FROM #Tmpdata WHERE ID=@Start           
         )))
     BEGIN
         SELECT @MaxOrder=MAX(ISNULL(OrderID,0))+1 FROM tblSectionLineItemTemplate WHERE TickerI

Richard Deeming

You haven't explained what you mean by "execution stuck" or "still no luck". If you want us to help you, you need to explain precisely what the problem is.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

thatraja

What's the issue? Any error? Include that in your question always.

thatraja

Coming soon1 | Coming soon2 | Coming soon3New

Mou_kol

Sir there was a problem in SP code. now everything is working fine after fixing. Thanks

Victor Nijegorodov

Mou_kol wrote:

here was a problem in SP code.

But you wrote yesterday:

Quote:

when i execute SP from SSMS then everything goes fine

:confused:

Lost User

You prototype with a trivial example; when that that works, you throw the kitchen sink at it; not before.

"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I