使用分隔符向 Sql 查询添加 where 子句 [英] Adding a where clause to Sql Query using delimiters

问题描述

我有以下存储过程，它接受一个使用两个分隔符的字符串.该过程提取数据并插入到数据库中.就像如果我有一个键值对 (样本输入字符串) 说 '10:1,11:2,12:3'，我也需要在 中寻找值 '10'>"Id" 列并在 "value" 列中插入值 1，依此类推.程序代码如下:

I have following stored procedure which accepts a string which uses two delimiters. The procedure extracts the data and inserts into DB. It's Like if I have a Key-Value Pair (Sample input string) say '10:1,11:2,12:3', I need to too look for value '10' in "Id" column and insert value 1 in the "value" column and so on. The code for procedure is as follows:

ALTER PROCEDURE [SQL_Delimiter_TestProcedure]
@inputStr VARCHAR(MAX) 
AS
BEGIN

DECLARE @t table (val varchar(500))

INSERT INTO @t (val)values (@inputStr)


;WITH CTE AS (
SELECT   
     Split.a.value('.', 'VARCHAR(500)') AS String  
 FROM  (SELECT   
         CAST ('<M>' + REPLACE([val], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  @t) AS A CROSS APPLY String.nodes ('/M') AS Split(a))
     INSERT INTO SQL_Delimiter_Test 
     select SUBSTRING(String,0,CHARINDEX(':',String)),REVERSE(SUBSTRING(reverse(String),0,CHARINDEX(':',reverse(String))))  from cte 

END

推荐答案

以下是简短版本:

首先，创建一个用户定义的表类型:

First, Create a user defined table type:

CREATE TYPE KeyValuePair As Table
(
    Key int PRIMARY KEY,
    Value int
)

然后将其用作存储过程中的表值参数:

Then use it as a table valued parameter in your stored procedure:

ALTER PROCEDURE [SQL_Delimiter_TestProcedure]
(
   @KeyValuePair as KeyValuePair readonly
)
AS
BEGIN

INSERT INTO SQL_Delimiter_Test (Id, Value)
SELECT Key, Value
FROM @KeyValuePair

END

可以在 链接上找到更多信息，该链接由<评论中的 href="https://stackoverflow.com/users/15498/damien-the-unbeliever">Damien_The_Unbeliever.

More information can be found on the link provided by Damien_The_Unbeliever in the comments.

这篇关于使用分隔符向 Sql 查询添加 where 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！