[RESOLVED] SQL Array Parameter

[cory_jackson]

I want to run a query that has a "Where in" clause with a variable array or list of integer. I could create the string, but I've been told before this is a bad practice and it should be done with a parameter and I do so with all my non-array variables. How do a create an array parameter and populate it? I envision something where the query clause would look like "Where in (@IDs).

[cory_jackson]

I forgot to say that. I know how to do the method of splitting the string into a table variable in SQL.

[techgnome]

Unless things have changed while I wasn't looking hte last few years, there isn't a way directly, and thast the way to do it is by passing in CSV string, and parsing it into a temp table or table var some ... and as soon as I typed that, it triggered something.... so I asked ChatGPT - https://chatgpt.com/share/75629807-6...4-1f4546558153

-tg

[cory_jackson]

Thanks TG. That's exactly what I did.
Have a great weekend.

[Zvoni]

Being prevented to look at tg's ChatGPT-Answer, there is a way to achieve what cory wants but with Parameters:
Using a staging-table!

You need a "staging"-table in your Database. In your usecase it only needs one Column: StageID as Integer
(Optional Attributes being UNIQUE, as well as having an unique index on that Column. Note: NO PRIMARY KEY!)

Algorithm
1) Empty out the staging table "DELETE FROM StageTable"
2) In a loop using Parameters you insert the ID's you'd use in your WHERE IN-Clause
Aircode (No idea what language you use, but i think you get the gist)

For i=0 To CountParams-1
   SQLcmd.Text="INSERT INTO StageTable (StageID) VALUES (:pID);"
   'Set Parameter-Value
   SQLcmd.Execute
Next

3) Now you can do your SELECT-Query with a simple INNER JOIN

SELECT MRT.ID, MRT.SomeField1, MRT.SomeField2......... 
FROM MyRealTable AS MRT
INNER JOIN StageTable AS ST ON MRT.ID=ST.StageID
ORDER BY SomeThing

4) (Optional) empty out the stage table again (see step1)

No danger for injection

As i said: No idea if that was the ChatGPT-Answer

[techgnome]

The prompt:

Using SQL Server, is there a way to pass in a list of values to a stored procedure so that it can be used as part of an IN clause of a query

The response:
Yes, you can pass a list of values to a stored procedure in SQL Server and use it as part of an IN clause. There are several approaches to achieve this, and I'll describe a couple of common methods:

1. Passing a Delimited String and Splitting It in SQL Server
You can pass a single string with delimited values (like a comma-separated list) and then split that string into a table or use it directly in the IN clause.

Example:
sql
Copy code

CREATE PROCEDURE dbo.GetRecordsByValues
    @ValueList NVARCHAR(MAX)
AS
BEGIN
    SELECT *
    FROM YourTable
    WHERE YourColumn IN (SELECT value FROM STRING_SPLIT(@ValueList, ','));
END

Usage:
sql
Copy code

EXEC dbo.GetRecordsByValues @ValueList = '1,2,3,4,5';

2. Using a Table-Valued Parameter (TVP)
Table-Valued Parameters allow you to pass a set of rows (like a list of values) to a stored procedure.

Step 1: Create a Table Type
sql
Copy code

CREATE TYPE dbo.ValueListType AS TABLE
(
    Value INT
);

Step 2: Create the Stored Procedure
sql
Copy code

CREATE PROCEDURE dbo.GetRecordsByValues
    @Values dbo.ValueListType READONLY
AS
BEGIN
    SELECT *
    FROM YourTable
    WHERE YourColumn IN (SELECT Value FROM @Values);
END

Usage:
sql
Copy code

DECLARE @MyValues dbo.ValueListType;
INSERT INTO @MyValues (Value) VALUES (1), (2), (3), (4), (5);

EXEC dbo.GetRecordsByValues @Values = @MyValues;

3. Using XML or JSON Input
You can also pass XML or JSON data to the stored procedure, parse it inside, and use it in the IN clause.

XML Example:
sql
Copy code

CREATE PROCEDURE dbo.GetRecordsByValues
    @XmlValues XML
AS
BEGIN
    SELECT *
    FROM YourTable
    WHERE YourColumn IN (
        SELECT Tbl.Col.value('.', 'INT') AS Value
        FROM @XmlValues.nodes('/Values/Value') Tbl(Col)
    );
END

Usage:
sql
Copy code

EXEC dbo.GetRecordsByValues @XmlValues = '<Values><Value>1</Value><Value>2</Value><Value>3</Value></Values>';

Each method has its use cases, so you can choose the one that best fits your needs.