SQL Get Specific Multiple ID's from TableAdapter using string (like IN expression)

Question

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.

Answer 1

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, ', ')
    )