SQL 使用字符串（如 IN 表达式）从 TableAdapter 获取特定的多个 ID

发布于 2025-01-20 16:03:21 字数 720 浏览 1 评论 0原文

我在程序中使用了数据集设计器。在我的数据库中，我有一个主键id_item，我想从数据库表（db）中获取一些特定的ID。使用来自数据集设计器的添加查询.. |表| tableadapter。

它创建了这样的查询：

SELECT 
    ID_Item, Item, SKU, Link, Cost, Notes, 
    fk_ID_Vendors, fk_ID_Process, fk_ID_ItemType, 
    Image, Valid, PNo, Purchase, CNo, Container, Dimension, fk_ID_Build
FROM
    Items
WHERE    
    ID_Item IN (@ID_ItemsLIST)

我命名了此fillmethodname = fill_id_specific。我将参数（ID_ITEMSLIST）更改为字符串。

如果id_itemslist =“ 32” - ＆gt;输出= 1行，
如果id_itemslist =“ 32，34” - ＆gt;输出= 1行，

我尝试了许多不同的迭代，使用撇号等。但是它将行不通。只是有一种简单的方法来做到这一点？

我可以使用语句编写单独的SQL连接。有效，很容易）。数据集设计人员中的某些东西并没有让这个简单的语句工作... :(我想将其封装在数据集设计器中...

真诚地；亚历克斯。

原文

I'm using a DataSet Designer in my program. In my database I have a primary key ID_Item and I want to fetch some specific ID's from the database table (dB). Using the Add Query.. from the DataSet Designer | Table | TableAdapter.

It creates a query like this:

SELECT 
    ID_Item, Item, SKU, Link, Cost, Notes, 
    fk_ID_Vendors, fk_ID_Process, fk_ID_ItemType, 
    Image, Valid, PNo, Purchase, CNo, Container, Dimension, fk_ID_Build
FROM
    Items
WHERE    
    ID_Item IN (@ID_ItemsLIST)

I've named this FillMethodName = Fill_ID_Specific. I've changed my Parameter (ID_ItemsList) to a String.

if ID_ItemsList = "32" -> output = 1 row,
if ID_ItemsList = "32, 34" -> output = 1 row

I've attempted many different iterations, using apostrophes, etc.. but it just will not work. There just must be a simple way to do this?!

I can write a separate SQL connection, using statement. Which works, easy enough). Something within the DataSet designer is not letting this simple statement work... :( I want to encapsulate this into the DataSet Designer...

Sincerely;
Alex.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

潜移默化 2025-01-27 16:03:21

您可以使用函数ufn_textaslist将文本转换为list

CREATE FUNCTION [dbo].[ufn_TextAsList]
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END

如何使用此函数？

SELECT * FROM [dbo].[ufn_TextAsList] ('32, 34', ', ')

output

item
32
34

如何与代码集成？

SELECT 
    ID_Item, Item, SKU, Link, Cost, Notes, 
    fk_ID_Vendors, fk_ID_Process, fk_ID_ItemType, 
    Image, Valid, PNo, Purchase, CNo, Container, Dimension, fk_ID_Build
FROM
    Items
WHERE    
    ID_Item IN (
       SELECT Item 
       FROM [dbo].[ufn_TextAsList] (@ID_ItemsLIST, ', ')
    )

You can use function ufn_TextAsList for convert text to list

CREATE FUNCTION [dbo].[ufn_TextAsList]
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END

How to use this function ?

SELECT * FROM [dbo].[ufn_TextAsList] ('32, 34', ', ')

Output

Item
32
34

How to integrate with your code ?

SELECT 
    ID_Item, Item, SKU, Link, Cost, Notes, 
    fk_ID_Vendors, fk_ID_Process, fk_ID_ItemType, 
    Image, Valid, PNo, Purchase, CNo, Container, Dimension, fk_ID_Build
FROM
    Items
WHERE    
    ID_Item IN (
       SELECT Item 
       FROM [dbo].[ufn_TextAsList] (@ID_ItemsLIST, ', ')
    )

回复收藏 0 原文

~没有更多了~