Stored Procedure Call Another Stored Procedure On RowCount?

I have two Stored Procedures sp_InsertInventory and sp_MergeInventory.

The idea is to quickly insert the dataset table values and let the merge sp process the insert/update accordingly.

I would like to eliminate the asp.net gui-freeze by using two sp's. Hopefully the merge sp would run on it's own and let the asp.net disconnect the sql connection.

So far sp_InsertInventory performs Inserts fine.

Problem: sp_InsertInventory never runs!

1. sp_InsertInventory is called from a ASP.NET Dataset Table Foreach loop
2. sp_MergeInventory is called from sp_InsertInventory when @rowsCount = @@RowCount

[asp.net]
      int rowsCount = dt.Rows.Count;

      foreach (DataRow row in dt.Rows)
      {
           using (SqlCommand command = new SqlCommand("sp_InsertInventory", cn))
           {
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@DealerId", SqlDbType.Int).Value = dealerId;
                command.Parameters.Add("@Vin", SqlDbType.NVarChar).Value = vin;
               command.Parameters.Add("@StockNumber", SqlDbType.NVarChar).Value = stockNumber;
               command.Parameters.Add("@RowsCount", SqlDbType.Int).Value = rowsCount;

               command.ExecuteNonQuery();
}}


[sp_sp_InsertInventory]
ALTER PROCEDURE [dbo].[sp_InsertInventory]
(		
       @DealerId               int,	
       @Vin 			nvarchar(20),
       @StockNumber	nvarchar(100),
       @RowsCount		int
)
AS
BEGIN		
      INSERT INTO [tbl_Inventory_TEMP] (Vin, StockNumber)
		VALUES (@Vin, @StockNumber);
						
		IF (@@ROWCOUNT = @RowsCount)
		BEGIN
		  EXEC sp_MergetblInventory @DealerId;
		END
END


[sp_MergeInventory]
ALTER PROCEDURE [dbo].[sp_MergetblInventory]
(		
	@DealerId		int
)
AS
BEGIN	

	SET NOCOUNT ON;

		BEGIN TRAN;

		    --Target Table
			MERGE tbl_Inventory AS T

			--Source Table
			USING tbl_Inventory_TEMP AS S

			ON (T.Vin = S.Vin) 

			WHEN NOT MATCHED BY TARGET  AND S.DealerId = 1 
				THEN
					INSERT (DealerId, Vin, StockNumber)
					VALUES (DealerId, Vin, StockNumber)

			WHEN MATCHED 
				THEN 
					UPDATE SET 
							DealerId = S.DealerId, 
							StockNumber = S.StockNumber;
			
			--OUTPUT $action, inserted.*, deleted.*;

		ROLLBACK TRAN;		
END

Open in new window

WorknHardrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>So far sp_InsertInventory performs Inserts fine.
>Problem: sp_InsertInventory never runs!
:: blank stare ::     is this a typo?

>sp_MergeInventory is called from sp_InsertInventory when @rowsCount = @@RowCount
Assuming the above should have said 'sp_MergeInventory never runs', and the INSERT uses VALUES so by definition would only insert one row, therefore @@RowCount would always be 1, is @rowsCount always being passed with a 1?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw you are missing a GO between the two ALTER DATABASE blocks.
0
WorknHardrAuthor Commented:
>>'sp_MergeInventory never runs'
Ooops :)

The sp_InsertInventory is called mutiple times from the asp.net foreach method. I'm hoping the @@rowcount would increment and ultimately equal the @rowscount value which is the dataset tables row count.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DBAduck - Ben MillerPrincipal ConsultantCommented:
How do you know it never runs?  In your merge sp it rolls back the transaction so nothing actually gets put into the tables.  I assume that is for testing.

But I am still not sure how you know it does not run without a COMMIT instead of a ROLLBACK in the sp.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I'm hoping the @@rowcount would increment
Nope, it does not incriment, it only reflects the previous query's rows.

To incriment, it's something like this...
Dim @i int = 0

WHILE @i >= 10
   begin
   --- Do stuff here
   SET @i = @i + 1
   end

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
@@rowcount doesn't increment... its the number of rows affected by the preceding sql statement....

you should always capture it to a temporary local variable and then do any tests you desire...

@@rowcount may not always = 1 on an insert (even with a single value clause)
since you may also have triggers active on the base table/object to which the insert is being directed...

so on 2 counts your logic is incorrect...

why don't you just use a merge statement anyway?  (ie just have a merge stored procedure)
0
WorknHardrAuthor Commented:
Thanks, I would run the SQL Management Studio debugger but I'm connected to a remote db and it will not allow me. I get this error:

"The EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)"
0
WorknHardrAuthor Commented:
Thank you all, I'm now using a Trigger on a special Upload table to run a Merge on the Inventory table...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.