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

我试图从 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_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

问题

我试图了解上述解决方案是否最接近可行的解决方案(目前它没有分割不以 ; 或 , 结尾的字符串的最后一个值)或者是否有更好的方法


慕姐4208626
浏览 95回答 2
2回答

吃鸡游戏

我认为更好的方法可能是将表变量直接传递给存储过程。为此,您必须创建一个类型来为输入表创建结构。我在下面重新创建了这个场景。请告诉我您是否可以利用它来实现您的目标。--Create test tablesCREATE 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 inputCREATE TYPE TableForInput as TABLE(    Item_Id int,    Item_Qty int);GO --Create/alter the stored procedureCREATE PROCEDURE dbo.usp_GetCardData@Input TableForInput READONLYASBEGIN     SELECT * FROM dbo.tblItem2 as t2 INNER JOIN @Input as i ON t2.Item_id = i.Item_idENDGO--Insert dummy data into the test tablesINSERT 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 procDECLARE @Data TableForInput;INSERT INTO @DataSELECT * FROM dbo.tblItem;exec dbo.usp_GetCardData @Data

大话西游666

您的见解确实有助于找到解决方案。我能够通过以下方式获得预期的行为: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
打开App,查看更多内容
随时随地看视频慕课网APP