将 SELECT 语句结果传递给 StorageProc 以进行临时表连接

Question

我正在尝试从DB-Server-1中的一个表中获取数据，并将其传递给在DB-SERVER-2上执行的StoredProcedure(从DB-Server-1连接到此数据(。

所以 我在不同的服务器上有 2 张桌子：

1. DB-服务器-1 ： tblItem

   -------------------
   item_id | item_qty 
   -------------------
   1231   | 2
   1232   | 4
   -------------------
   

2. DB-Server-2 ： tblItemDetails

   ----------------------------------------
   item_detail_id | item_id | item_data
   ----------------------------------------
   1             | 1231    |  TEST_DATA_1
   2             | 1232    |  TEST_DATA_2
   ----------------------------------------
   

现在我想从数据库服务器-1获取数据(基本选择查询(

SELECT item_id, item_qty
FROM tblItem
WHERE item_id IN (1231, 1232);

并将其传递给存储过程：spSetItemQuantityInItemDetails

CREATE PROCEDURE [dbo].[spUpdatePOCartons]
(
@valueList VARCHAR(MAX)
)
.
.
-- Now trying to split string and create a temp table
-- This temp table will later be used in the SP to join with tblItemDetails to set item_qty
DECLARE @temp TABLE (
item_id int,
item_qty int
);
DECLARE @pos1 INT
DECLARE @len1 INT
DECLARE @value1 varchar(8000)
DECLARE @pos2 INT
DECLARE @len2 INT
DECLARE @value2 varchar(8000)
SET @valueList = '1,4;2,5;3,14;';
set @pos1 = 0
set @len1 = 0
WHILE CHARINDEX(';', @valueList, @pos1+1)>0
BEGIN
set @len1 = CHARINDEX(';', @valueList, @pos1+1) - @pos1
set @value1 = SUBSTRING(@valueList, @pos1, @len1)
--SELECT @pos, @len, @value /*this is here for debugging*/
PRINT @value1;
-----------
set @pos2 = 0
set @len2 = 0
WHILE CHARINDEX(',', @value1, @pos2+1)>0
BEGIN
set @len2 = CHARINDEX(',', @value1, @pos2+1) - @pos2
set @value2 = SUBSTRING(@value1, @pos2, @len2)
--SELECT @pos, @len, @value /*this is here for debugging*/
PRINT @value2;
set @pos2 = CHARINDEX(',', @value1, @pos2+@len2) +1
END
------------
set @pos1 = CHARINDEX(';', @valueList, @pos1+@len1) +1
END

问题

我正在尝试了解上述解决方案是否最接近工作(目前它没有拆分不以 ; 或 ， 结尾的字符串的最后一个值( 或者有没有更好的方法

Answer 1

我认为更好的方法可能是将表变量直接传递给存储过程。为此，您必须创建一个类型，用于为输入表创建结构。我在下面重新创建了这个场景。让我知道您是否可以解决这个问题来实现您的目标。

--Create test tables
CREATE TABLE dbo.tblItem (item_id int, item_qty int);
CREATE TABLE dbo.tblItem2 (item_detail_id int, item_id int, item_details varchar(50));
GO
--Create the type that can be used as input
CREATE TYPE TableForInput as TABLE
(
Item_Id int,
Item_Qty int
);
GO 
--Create/alter the stored procedure
CREATE PROCEDURE dbo.usp_GetCardData
@Input TableForInput READONLY
AS
BEGIN 
SELECT * FROM dbo.tblItem2 as t2 INNER JOIN @Input as i ON t2.Item_id = i.Item_id
END
GO
--Insert dummy data into the test tables
INSERT INTO dbo.tblItem values (1231, 2), (1232, 4);
INSERT INTO dbo.tblItem2 VALUES (1, 1231, 'TEST_1'), (2, 1232, 'TEST2')
-- The below would mimic the client side.
-- Declare a local temporary table, select data into it
--      And then pass this as a parameter to the stored proc
DECLARE @Data TableForInput;
INSERT INTO @Data
SELECT * FROM dbo.tblItem;

exec dbo.usp_GetCardData @Data

Answer 2

非常感谢大家。您的见解确实有助于找到解决方案。 我能够通过以下方式获得预期的行为：

IF OBJECT_ID('tempdb..#tval') IS NOT NULL DROP TABLE #tval;
CREATE TABLE #tval (val1 INT, val2 INT);
declare @s varchar(1000)
set @s = '1,11;2,22'
;WITH cte AS (
select value
from string_split(@s, ';')
)
INSERT INTO #tval
SELECT
MAX(CASE WHEN ord=1 THEN v END),
MAX(CASE WHEN ord=2 THEN v END)
FROM cte
CROSS APPLY (SELECT value AS v, ROW_NUMBER() OVER(ORDER BY 1/0) AS ord
FROM  STRING_SPLIT([value], ',')) s
GROUP BY value;
SELECT * from #tval