存储过程代码单独执行正常，节点调用无数据更新问题

Let’s break down why your stored procedure isn’t inserting data into zloc_InvValue when executed directly, even though running the inner code works perfectly. Here are the most likely issues and actionable fixes:

1. Case Sensitivity Mismatch in Pivot Columns #

This is a top culprit if your database uses a case-sensitive collation.

Looking at your code:

• When populating #temp, you generate whse values like 'NS mtl' (lowercase "mtl") by concatenating 'NS ' with the lowercase whse values from your openquery results.
• But in the pivot step, you reference columns like [NS MTL] (uppercase "MTL").

Case-sensitive collations treat these as distinct strings, so the pivot fails to match any rows. This leaves #stock1 with NULL values or no usable data for the final insert.

Fix:
Standardize the case of your whse values to match the pivot column names. Modify the #temp creation code to use UPPER():

select cono, UPPER(whse) as whse, value into #temp from (
select 1 as cono, whse, SUM(total_value) as value from #nStock group by whse
union all
select 1 as cono, whse, SUM(qty * cost) as value from #Stock group by whse
union all
select 1 as cono,UPPER('S-3PL') as whse, SUM(case when item_type = 's' then value else 0 end) as value from #3pl
union all
select 1 as cono,UPPER('NS-3PL') as whse, SUM(case when item_type = 'n' then value else 0 end)as value from #3pl
) "a";

This ensures #temp.whse values are uppercase, perfectly matching the pivot column names like [NS MTL].

2. Permission Differences Between Direct Execution and Stored Procedure #

When you run the code manually, you’re using your own user account permissions. But stored procedures run under the context of their owner (or the executing user, depending on settings). If the procedure’s owner lacks:

• Access to the twllive linked server
• INSERT permissions on zloc_InvValue
• Permissions to create temporary tables

The procedure might silently fail to retrieve data or insert rows, even though it returns a "success" status.

Fix:

• Check the stored procedure’s owner (use sp_helptext Update_invValue or view properties in SSMS) and ensure they have all necessary permissions for linked server access and table writes.
• Alternatively, alter the procedure to execute with elevated permissions:

  ALTER PROCEDURE [dbo].[Update_invValue]
  WITH EXECUTE AS OWNER
  AS
  BEGIN
  -- Rest of your procedure code
  END
  

3. Debugging to Pinpoint Missing Data #

To identify exactly where the pipeline breaks, add debugging statements to your procedure to inspect intermediate results. For example:

-- After creating #nStock
PRINT '#nStock row count: ' + CAST((SELECT COUNT(*) FROM #nStock) AS VARCHAR(10));
SELECT * FROM #nStock;

-- After creating #Stock
PRINT '#Stock row count: ' + CAST((SELECT COUNT(*) FROM #Stock) AS VARCHAR(10));
SELECT * FROM #Stock;

-- After creating #temp
PRINT '#temp contents:';
SELECT * FROM #temp;

-- After creating #stock1
PRINT '#stock1 contents:';
SELECT * FROM #stock1;

Run the procedure again and review these outputs. If any temp table has no rows or unexpected NULLs, that’s where your issue originates.

4. Temporary Table Naming Edge Case #

While less likely, some SQL Server versions can have parsing quirks with temporary tables starting with a number (like #3PL). Try renaming it to #ThreePL and updating all references in the code to rule this out.

Final Notes #

After applying these fixes, recompile the stored procedure and test it again. If the issue persists, check the SQL Server error log for silent errors that might not be returned to the client.

内容的提问来源于stack exchange，提问作者Marc Landry