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

Question

我试图从 DB-Server-1 中的一个表获取数据，并将其传递给在 DB-SERVER-2 上执行的 StoredProcedure（该存储过程从 DB-Server-1 连接到该数据）。所以，我在不同的服务器上有 2 个表：DB-Server-1：表项-------------------item_id | item_qty ------------------- 1231   | 2 1232   | 4-------------------DB-Server-2：tblItemDetails----------------------------------------item_detail_id | item_id | item_data---------------------------------------- 1             | 1231    |  TEST_DATA_1 2             | 1232    |  TEST_DATA_2----------------------------------------现在我想从 DB-Server-1 获取数据（基本选择查询）SELECT item_id, item_qtyFROM tblItemWHERE item_id IN (1231, 1232);并将其传递给存储过程：spSetItemQuantityInItemDetailsCREATE 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_qtyDECLARE @temp TABLE (item_id int,item_qty int);DECLARE @pos1 INTDECLARE @len1 INTDECLARE @value1 varchar(8000)DECLARE @pos2 INTDECLARE @len2 INTDECLARE @value2 varchar(8000)SET @valueList = '1,4;2,5;3,14;';set @pos1 = 0set @len1 = 0WHILE 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