我試圖從 DB-Server-1 中的一個(gè)表獲取數(shù)據(jù)，并將其傳遞給在 DB-SERVER-2 上執(zhí)行的 StoredProcedure（該存儲(chǔ)過程從 DB-Server-1 連接到該數(shù)據(jù)）。所以，我在不同的服務(wù)器上有 2 個(gè)表：DB-Server-1：表項(xiàng)-------------------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----------------------------------------現(xiàn)在我想從 DB-Server-1 獲取數(shù)據(jù)（基本選擇查詢）SELECT item_id, item_qtyFROM tblItemWHERE item_id IN (1231, 1232);并將其傳遞給存儲(chǔ)過程：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問題我試圖了解上述解決方案是否最接近可行的解決方案（目前它沒有分割不以 ; 或 , 結(jié)尾的字符串的最后一個(gè)值）或者是否有更好的方法
2 回答
吃雞游戲
TA貢獻(xiàn)1829條經(jīng)驗(yàn) 獲得超7個(gè)贊
我認(rèn)為更好的方法可能是將表變量直接傳遞給存儲(chǔ)過程。為此，您必須創(chuàng)建一個(gè)類型來為輸入表創(chuàng)建結(jié)構(gòu)。我在下面重新創(chuàng)建了這個(gè)場景。請(qǐng)告訴我您是否可以利用它來實(shí)現(xiàn)您的目標(biāo)。
--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
大話西游666
TA貢獻(xiàn)1817條經(jīng)驗(yàn) 獲得超14個(gè)贊
您的見解確實(shí)有助于找到解決方案。我能夠通過以下方式獲得預(yù)期的行為：
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
- 2 回答
- 0 關(guān)注
- 174 瀏覽
添加回答
舉報(bào)
0/150
提交
取消