通过一个逗号包含逗号 [英] Passing a paramtere that has comma in it

问题描述

以下是我的表格：



表1

Following is my table:

TABLE 1

facility | CPTCode
1        | 45
2        | 46
3        |456,678

现在我有另一个表从该表中读取值并生成另一个报告。因此，对于设施1和2，它非常直接



表测试

Now I have another table that reads the values from this table and generate another report. So for facility 1 and 2, its pretty straight forward

TABLE TEST

facility | CPTCode |Something
1        | 45      |Apple
2        | 46      |Orange
3        | 456     |Banana
3        | 678     |Strawberry

select * from TEST 
where facility = '1' and CPTCode = 45

但是如何编写我的查询来处理逗号

查询应该像

but how do i write my query where it takes care on comma
The query should be like

Select * from TEST 
where facility = '3' and CPTCode in('456','678')

如何写入的sql？

How do I write this in sql?

SELECT facility , TxnSerDate, TxnBchDate, TxnProcedure, TxnAmount, CPTCode 	
FROM TEST  
where facility = @facility  and CPTCode = @CptCode

需要帮助写作

Need help writing the

SELECT facility , TxnSerDate, TxnBchDate, TxnProcedure, TxnAmount, CPTCode 	
FROM TEST  
where facility = @facility  and CPTCode = @CptCode = @CPTCode statement

我尝试过：

What I have tried:

SELECT facility , TxnSerDate, TxnBchDate, TxnProcedure, TxnAmount, CPTCode 	
FROM TEST  
where facility = @facility  and CPTCode = @CptCode

推荐答案

好的。根据提出的内容。我猜，在存储过程中有一个像'456,678'这样的参数，你想把参数放到查询中，并期望查询返回CPTCode 456和678的行？如果是这种情况，这里是一个例子。

Ok. Based on what presented. I'm guessing, there is a parameter like '456,678' in the Stored Procedure, and you want to put the parameter into the query and expect the query to return the rows with CPTCode 456 and 678? If the the case, here is an example.

DECLARE @temp TABLE  (
 Facility INT IDENTITY(1,1),
 CPTCode VARCHAR(30),
 Something VARCHAR(50)
)

INSERT INTO @temp
	SELECT 45, 'Apple' UNION
	SELECT 46, 'Orange' UNION
	SELECT 456, 'Pear' UNION
	SELECT 577, 'Banana' UNION
	SELECT 678, 'Strawberry'

	--parameter
DECLARE @CptCode  VARCHAR(30)
SET @CptCode  = '45,456,577'

--comma separated string into rows
DECLARE @cptCodeRows TABLE(CPTCode VARCHAR(30));
;WITH CTE_CPTCode
AS
(
    SELECT [xml_val] = CAST('<b>' + REPLACE(@CptCode ,',','</b><b>') + '</b>' AS XML)
)
INSERT INTO @cptCodeRows
	SELECT  
			ProgramId = col.value('.','VARCHAR(30)')
	FROM CTE_CPTCode
	CROSS APPLY [xml_val].nodes('/b') CA(col)

	--get all the records in @CptCode  
SELECT * FROM @temp t JOIN @cptCodeRows c
	ON t.CPTCode = c.CPTCode

输出：

Output:

Facility	CPTCode	  Something	CPTCode
1	        45	      Apple	    45
3	        456	      Pear	    456
4	        577	    Banana	    577

我建​​议您添加一个联结表，而不是在单个列中存储多个外键，而是将每个键存储在单个行中。请参阅多对多（数据模型） - 维基百科 [ ^ ]



使用联结表你的查询就像

Instead of storing multiple foreign keys inside a single column, I would recommend adding a junction table where you store each key on an individual row. See Many-to-many (data model) - Wikipedia[^]

Using a junction table your query would simply be something like

SELECT ...
FROM Table1,
     JunctionTable1,
     Test
WHERE JunctionTable1.Facility = Table1.Facility
AND   JunctionTable1.CptCode = Test.CptCode

这篇关于通过一个逗号包含逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！